Oracle: What does 'execute immediate' mean?

Question

What is the significance of execute immediate in PL/SQL when used with DML and DDL statements? Does execute immediate implicitly commit to a database like DDL statements do ?

The following is an example I have encountered:

SELECT 'TRUNCATE TABLE BD_BIDS_APPR_DET' INTO V_SQL1 FROM DUAL;
EXECUTE IMMEDIATE V_SQL1;

SELECT 'TRUNCATE TABLE BD_BIDS_SYS_DET' INTO V_SQL1 FROM DUAL;
EXECUTE IMMEDIATE V_SQL1;

SELECT 'TRUNCATE TABLE BD_BIDS_EXT_DET' INTO V_SQL1 FROM DUAL;
EXECUTE IMMEDIATE V_SQL1;

Answer 1

It is for running native dynamic SQL.

For DML you'd use it when running statements that you don't have available at compile time, e.g. if the column list is based on a selection from the user.

In your case it's being used because DDL cannot be run as static SQL from within PL/SQL. Only certain query, DML and TCL commands are valid. Anything else has to be treated as dynamic.

I'd say it's rare to need to use DDL from a PL/SQL block. TRUNCATE might be reasonable; if you find anything creating or dropping objects on the fly then that might be more of a concern as it can suggest a suboptimal data model.

EXECUTE IMMEDIATE itself does not automatically commit; but if you execute DDL then that will behave the same as if you ran it outside PL/SQL, so it will commit in your case, yes.

Incidentally, I'm not sure why your code is using an intermediate variable to hold the statement; that's useful if you want to display what it's going to run maybe, but you don't seem to be doing that. What you have could be done as:

EXECUTE IMMEDIATE 'TRUNCATE TABLE BD_BIDS_EXT_DET';

i.e. without using V_SQL_1 at all.