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.
1 2 3 4
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:
1 2 3 4
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:
1 2 3
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.