Dynamic SQL

We got two ways for the Dynamic SQL.

1. EXECUTE IMMEDIATE

2.DBMS_SQL

1. EXECUTE IMMEDIATE
Note:- This type of Dynamic SQL would not work in the 10.7 version Database.

For the Select statement
Note:- Sample Examples code based on the EMP and DEPT table in the scott schema.
Example:-
----------
DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select max(sal) from emp
where deptno = :l_deptno'
INTO L_SAL
USING L_DEPTNO;
DBMS_OUTPUT.PUT_LINE(L_SAL);
END;

For the Insert statement
Note:- Sample Examples code based on the EMP and DEPT table in the scott schema.
Example:-
-----------
DECLARE
L_ENAME VARCHAR2(20) DEFAULT 'PHANI';
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO EMP(ENAME,EMPNO,DEPTNO) VALUES
(:L_ENAME,:L_EMPNO,:L_DEPTNO)'
USING L_ENAME,
L_EMPNO,
L_DEPTNO;
END;

For the Update Statement
Note:- Sample Examples code based on the EMP and DEPT table in the scott schema.
Example:-
-----------
DECLARE
L_ENAME VARCHAR2(20) DEFAULT 'PHANI';
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE 'UPDATE EMP
SET ENAME = ''RAHUL''
WHERE ENAME = :l_ENAME'
USING L_ENAME;
END;

2.DBMS_SQL
For the Select statement
Note:- Sample Examples code based on the EMP and DEPT table in the scott schema.
Example:-
DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER;
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
BEGIN
--- Code converted in dynamic SQL start(phani).
L_SQL := 'select max(sal) from emp where deptno = :l_deptno';
L_CUR := dbms_sql.OPEN_CURSOR;
dbms_sql.PARSE( L_CUR, L_SQL, dbms_sql.NATIVE );
dbms_sql.BIND_VARIABLE( L_CUR, ':l_deptno', L_DEPTNO );
-- describe defines
dbms_sql.DEFINE_COLUMN( L_CUR, 1, L_SAL );
-- execute
L_RC := dbms_sql.EXECUTE( L_CUR );
LOOP
-- fetch a row
IF dbms_sql.FETCH_ROWS( L_CUR ) > 0 THEN
-- fetch columns from the row
dbms_sql.COLUMN_VALUE( L_CUR, 1, L_SAL );
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.CLOSE_CURSOR( L_CUR );
dbms_output.PUT_LINE( L_SAL );
--- Code converted in dynamic SQL end (phani).
END;

For the Insert statementNote:- Smple Examples code based on the EMP and DEPT table in the scott schema.
Example:-
DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_empno NUMBER := 1;
g_ename VARCHAR2(30) := 'REDDY';
g_deptno NUMBER := 10;
BEGIN
--- Code converted in dynamic SQL start.
l_sql:= 'insert into emp
(EMPNO,
ENAME,
DEPTNO)
VALUES
( :p_empno,
:p_ename,
:p_deptno)';

l_cur := dbms_sql.open_cursor;
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ':p_empno', g_empno);
dbms_sql.bind_variable(l_cur, ':p_ename', g_ename);
dbms_sql.bind_variable(l_cur, ':p_deptno',g_deptno);
-- execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);
END;

For the Update StatementNote:- Sample Examples code based on the EMP and DEPT table in the scott schema.
Example:-
DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_new_ename VARCHAR2(20) := 'REDDY01';
g_old_ename VARCHAR2(20) := 'REDDY';
BEGIN
--- Code converted in dynamic SQL start.

l_sql:= 'update emp
set ename = :p_new_ename
where ename = :p_old_ename';

l_cur := dbms_sql.open_cursor;
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);

dbms_sql.bind_variable(l_cur, ':p_new_ename',g_new_ename);
dbms_sql.bind_variable(l_cur, ':p_old_ename',g_old_ename);

--- execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);

--- Code converted in dynamic SQL end.
END;