, ,

DBMS_SQL

For the Select statement
Note:- I have created the 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 statement

Note:- I have created the 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_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 Statement
Note:- I have created the 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;

/by