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:


2 comments:

  1. 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;"

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

    Your syntax is not working with JDBC.

    Andrejus

    ReplyDelete