Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Friday, 12 October 2018

Oracle PLSQL Dynamic SQL

0 comments



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;


 




Continue reading >>