Oracle PLSQL Dynamic SQL

Binding In PL\SQL

Oracle needs values for any variables listed in the statement

Example:

select empno,ename from emp where id=emp_id;

Oracle needs a value for emp_id. The process of obtaining these values is called binding variables.

Compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time.

Static Binding

Binding at compile time, called static or early binding.

Increases efficiency

Dynamic Binding

Binding at run time, called dynamic or late binding.

Increases flexibility

Issue Using Directly DDL in PLSQL

For Example:

CREATE PROCEDURE create_table AS

BEGIN

CREATE TABLE dept (deptno NUMBER(2), ...); -- illegal Table is undefined until the procedure is executed at run time.

…

END;

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

BEGIN

DROP TABLE table_name; -- illegal Table name is unknown until the procedure is executed.

...

END;

DDL Can’t Use in PL\SQL Directly

Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE.

Session control language (SCL) statements, such as ALTER SESSION and SET ROLE.

BEGIN

CREATE TABLE dept_temp (deptno NUMBER(2), dname VARCHAR2(30),loc VARCHAR2(30));

END;

IF you use DDL directly you will get following error

ORA-06550: line 2, column 1:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following

Using DDL in PL/SQL by using Dynamic SQL

BEGIN

Execute immediate 'CREATE TABLE dept_temp (deptno NUMBER(2), dname VARCHAR2(30),loc VARCHAR2(30))';

END;