SQL IN PL/SQL

The only statements allowed directly in pl/sql are DML and TCL.

BINDING

Binding a variable is the process of identifying the storage location associated with an identifier in the program.

Types of binding

• Early binding
• Late binding

• Binding during the compiled phase is early binding.
• Binding during the runtime phase is late binding.
• In early binding compile phase will take longer because of binding work but the Execution is faster.
• In late binding it will shorten the compile phase but lengthens the execution time.
• PL/SQL by default uses early binding.
• Binding also involves checking the database for permissions to access the object

Referenced.

DYNAMIC SQL

• If you use DDL in pl/sql it validates the permissions and existence if requires during compile time which makes invalid.
• We can avoid this by using Dynamic SQL.
• Dynamic SQL allows you to create a SQL statement dynamically at runtime.

Two techniques are available for Dynamic SQL.

• Native Dynamic SQL
• DBMS_SQL package

USING NATIVE DYNAMIC SQL

USING EXECUTE IMMEDIATE