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;
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;
Leave a Reply
Want to join the discussion?Feel free to contribute!