Monday, April 25, 2011

Invoking Stored Procedures and Functions with Named Variable Parameters

This example will explain briefly, how to use named variable parameters for PL/SQL calls. As for example, this simple PL/SQL procedure with IN and OUT parameter will be used:

Its better to pass parameters to PL/SQL procedure through named variable parameters, instead of using indexes, your code becomes much readable then. Its not described in ADF developer guide - 37.5 Invoking Stored Procedures and Functions and developers may get confused, how to pass values through named variable parameters. If developer would try to use variable=? syntax:

Runtime error will be generated for such SQL callable statement:

Correct way is to use :variable syntax for SQL callable statement:

PL/SQL procedure is completed successfully:


Anonymous said...

I notice you're still using positional parameters in the PL/SQL call. Would it be possible to use named parameters instead? e.g.

"begin greeting_proc(input_text=>:text_param, output_text=>:msg_text); end;"

Andrej Baranovskij said...

Yes, I'm using named parameters in the example above.

Your syntax is not working with JDBC.