sql_mode=ORACLE: IN, OUT, IN OUT modes for dynamic SQL bind arguments

execute_immediate_statement ::=

EXECUTE_IMMEDIATE dynamic_string

   [ INTO { define_variable [, define_variable ...] | record_name } ]

   [ USING [ IN | OUT | IN OUT ] bind_argument

       [, [ IN | OUT | IN OUT ] bind_argument] ... ]

   [ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];

DECLARE

  plsql_block VARCHAR2(500);

  new_deptid  NUMBER(4);

  new_dname   VARCHAR2(30) := 'Advertising';

  new_mgrid   NUMBER(6)    := 200;

  new_locid   NUMBER(4)    := 1700;

BEGIN

 -- Dynamic PL/SQL block invokes subprogram:

  plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';

 /* Specify bind arguments in USING clause.

    Specify mode for first parameter.

    Modes of other parameters are correct by default. */

  EXECUTE IMMEDIATE plsql_block

    USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;

END;

/