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;
