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