Execute Immediate and Dynamic SQL

When working with PL/SQL you may find that you would like to iteratively execute a query with different parameters.  Well, got you good news.  You can!

There’s an instruction that will make those ideas come true.

EXECUTE IMMEDIATE is an Oracle implementation that allows the developer to prepare a statement and then execute it from inside the PL/SQL block, defining also input and/or output parameters.

More so, this instruction will generate some tracing info that can be very useful when trying to model the business logic in the pl/sql program.

In its most basic form, it’s used like this:

EXECUTE IMMEDIATE <sql_query>;

This <sql_query> can be as simple or as complex as we want.  It can even be another pl/sql block.  So, you can use a variable to store the statement, let’s say v_smnt and then execute it with:

EXECUTE IMMEDIATE

v_smnt;

Even more, you can use bind variables or parameters with the simple addition of the USING and RETURNING INTO keywords followed by the pl/sql environment variables you want to use.

All these features make this command very powerful.  It also keep some tracking info in memory that you can access through some special variables, like SQL%ROWCOUNT.

Now that you know what would you be looking for, you can visit the next few links for more info on this command and its uses.

Oracle Docs - Command Diagram and elements explanation

Oracle Docs - Dynamic SQL explanation and some examples

And, of course, you can make questions here and I’ll answer as soon as I can.