Using dynamic SQL statements from PL/SQL

Sometimes you need to execute dynamic SQL statements. Starting from Oracle8i you can accomplish this task using the EXECUTE IMMEDIATE statement. Here are three examples of how you can take advantage of this great statement.

    
sql_select := 'SELECT * FROM your_table WHERE field1 = :1';
EXECUTE IMMEDIATE sql_select
INTO your_cursor
USING your_parameter_for_field1;

In this first example I showed how you can use EXECUTE IMMEDIATE to execute the query and put the result into a cursor. The USING your_parameter_for_field1 part replaces the :1 bind variable with the value contained in the your_parameter_for_field1 parameter.

Now let’s see another example:

   
sql_select := 'SELECT * FROM your_table WHERE field1 = :1 AND field2 = :2';
EXECUTE IMMEDIATE sql_select
INTO your_cursor
USING your_parameter_for_field1, your_parameter_for_field2;

As you can see this example is similar to the previous one but here I showed how you can use more than one parameter.

One last example:

     
sql_insert := 'INSERT INTO your_table VALUES (:1, :2)';
EXECUTE IMMEDIATE sql_insert
USING your_parameter_for_field1, your_parameter_for_field2;

This last example shows how you can use EXECUTE IMMEDIATE to execute an INSERT statement. Here you don’t need an explicit cursor. Of course, in a similar way, you can use EXECUTE IMMEDIATE to execute UPDATE and DELETE statements as well.

Of course, you could have accomplished the “same” result using string concatenation but binding the variables using the method just shown is much better from a performance viewpoint.

One last note: you CANNOT use bind variables to replace actual object names like table names, columns and so on. In this case you can turn back to string concatenation.