Introduction of DYNAMIC SQL

Introduction of DYNAMIC SQL:-
------------------------------------

If we have to create some PROCEDURE, FUNCTION or PACKAGE and run them, then we have to do two steps.

1) Compile the code.
2) Execute the code which you have compiled.

Errors can occur at any above steps.

At the compile Time (Step 1), system will check for the syntax of the code and also if all the objects used in the code are exisiting in database or not.

If you want to hide the objects at the compile time so that you do not get the error message at the compile time (step 1) then use DYNAMIC SQL.

Difference between DBMS_SQL and EXECUTE IMMEDIATE
-----------------------------------------------------------------------

EXECUTE IMMEDIATE type of Dynamic SQL would not work in the old versions of Oracle Database like 10.7 or older then this version.

DBMS_SQL type of Dynamic SQL would work in all the version of Oracle Database.

                                             DYNAMIC SQL HOME