, ,

EXECUTE IMMEDIATE

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

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; 
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:- I have created the 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:- I have created the 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;
/by
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply