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.
USING your_parameter_for_field1 part replaces the
:1 bind variable with the value contained in the
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
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.