Binding refers to the linking of program values to variables at runtime. This is done by means of bind variables. Using bind variables in native dynamic SQL makes the code execute faster and makes it easier to maintain. It's faster because the same SQL statement is executed with different values of the bind variables, so that a single cursor is shareable by multiple SQL statements. Also, binding takes care of data type conversion implicitly since native data types are involved. Otherwise, you should use the conversion functions such as TO_DATE, TO_CHAR. You specify bind variables using a colon (:) followed by the variable name, and you specify the actual values using the USING clause. Note that the actual values can also be held in PL/SQL variables, but these variables are not prefixed with a colon.

Here are the steps involved:

1. Specify the bind variables while constructing the SQL statement dynamically. It's here that you prefix the bind variable names with the colon.

2. Specify the actual values for the bind variables using the USING clause of the EXECUTE IMMEDIATE statement or OPEN-FOR statement. These are called bind arguments.

Here's an example of using bind variables:

Dyn_upd_string1 := ' UPDATE '||dyn_table_name2||
               ' SET total_qty = :total_qty, total_price = :total_price WHERE '||
                         ' order_id = :input_order_id ';

  EXECUTE IMMEDIATE dyn_upd_string1
      USING v_total_quantity, v_total_price, i_order_id;

where :total_qty, :total_price, and :input_order_id are three bind variables. The actual values are held in the PL/SQL variables v_total_quantity, v_total_price, and i_order_id.

The following points are worth noting:

• The bind variables aren't declared.

• The bind variables are prefixed with a colon.

Binding works as follows: The dynamic string specified in the EXECUTE IMMEDIATE statement is first parsed at runtime. Next, syntax and semantic references are resolved. This is why you can't specify schema object names by means of bind variables. The runtime PL/SQL engine can't determine such names correctly. At the time of execution, the PL/SQL engine replaces the bind variable with the value in the corresponding variable specified in the USING clause.

Bind arguments can be of three types: IN, OUT, and IN OUT. These are like the IN, OUT, and IN OUT parameter modes for subprograms. For dynamic queries, native dynamic SQL supports only IN bind arguments. However, for dynamic DML statements using a RETURNING clause, OUT bind arguments are allowed.

Bind variables are permitted in dynamic PL/SQL blocks and can be supplied in place of actual parameters of a subprogram that's being executed as dynamic PL/SQL. However, the type of each bind argument must be the same as the corresponding parameter mode.

Dynamic PL/SQL

Native dynamic SQL supports the execution of PL/SQL blocks dynamically—that is, executing anonymous PL/SQL blocks supplied as strings enclosed within BEGIN … END depending on runtime variable values. Also, subprograms whose names are dynamically constructed can be executed using dynamic PL/SQL. A third use of dynamic PL/SQL is executing bulk dynamic SQL statements by enclosing them within BEGIN … END.

The statement

EXECUTE IMMEDIATE 'BEGIN ... END;' USING ;

is used for dynamic PL/SQL. Note that the enclosing BEGIN … END is necessary.

Here are the rules for executing dynamic PL/SQL:

• The dynamic PL/SQL block must start with a DECLARE or BEGIN and end with an END followed by a semicolon (;). If the dynamic SQL string ends with a semicolon, it's treated as a PL/SQL block.

• Dynamic PL/SQL blocks do not have access to local variables defined within the block. Hence, only PL/SQL global variables defined in a package and stand-alone subprogram names can be referenced inside the dynamic PL/SQL block.

The procedure update_dyn_for_all_orders listed previously accepted region_name as an input parameter and the procedure update_dyn_global executed this procedure for all regions in a loop. Instead, you could write individual procedures for each region with their names dynamically constructed by suffixing them with each region name. Again, you can use a cursor loop to obtain the individual region names. I call the procedure that does this update_dyn_global2. Here's the code for this procedure:

Create or replace Procedure update_dyn_global2
                                               (retcd OUT NUMBER,
                                                errmsg OUT VARCHAR2)
Is
  CURSOR csr_region IS
     SELECT region_name FROM region_tab;
Dyn_proc_name VARCHAR2(100);
Dyn_plsql_string VARCHAR2(1000);
BEGIN
   FOR idx IN csr_region LOOP
   EXECUTE IMMEDIATE ' BEGIN update_dyn_'||replace(trim(idx.region_name),' ','_')||' (:1, :2) ; END;'
     USING OUT retcd, OUT errmsg;
   IF retcd <> 0 THEN
     EXIT;
   END IF;
 END LOOP;
EXCEPTION WHEN OTHERS THEN
  Retcd := SQLCODE;
  Errmsg := SQLERRM;
END;
/

This procedure does the following:

• Declares a cursor for all the region names in the region_tab table.

• For each row in the cursor, it dynamically constructs a procedure name by concatenating the region name to the constant update_dyn_ and then dynamically executes this procedure. This process uses two bind parameters corresponding to the OUT parameters for each procedure.

• Outputs a return code of 0 on success in the form. of an OUT parameter named retcd. On error it outputs the corresponding SQLCODE and SQLERRM in the form. of two OUT parameters, retcd and errmsg.

Here, the individual procedures generated dynamically should have already been created by a similar procedure called create_proc_dyn_global.

Exception Handling in Native Dynamic SQL

I should note two points about exception handling when using native dynamic SQL:

• An exception handler in an enclosing block can catch exceptions in native dynamic SQL statements enclosed within it.

• Always define an exception handling section by specifying it an enclosing PL/SQL block.

As an example, consider the procedure update_dyn_global. The exception handler

EXCEPTION WHEN OTHERS THEN
  Retcd := SQLCODE;
  Errmsg := SQLERRM;

between the enclosing BEGIN … END will trap any exceptions that arise due to the failure of the dynamic PL/SQL statement or normal PL/SQL failure. To trap each of these exceptions separately, you can define individual exception handlers by specifying a nested block in the outer BEGIN … END as follows:

BEGIN
  EXECUTE IMMEDIATE ' BEGIN '||dyn_plsql_string ||' END; '
    USING OUT retcd, OUT errmsg;
    IF (retcd <> 0) THEN
      EXIT;
    END IF;
EXCEPTION WHEN OTHERS THEN
  Retcd := SQLCODE;
  Errmsg := 'ERR: '||dyn_plsql_string||' - '||SQLERRM;
END;

You may not need to do this in the case of more generic exception handlers, because irrespective of where the exception was raised, control branches to the exception handling section. However, this code helps to track each exception individually and improves block structuring.