Execute Immediate - How to use INTO clause for Single or multiple columns value return

 
--====================================================
DML :- SELECT / INSERT / UPDATE / DELETE
1. Execute Immediate with INTO clause in Oracle
--====================================================
 
• If the dynamic SQL statement is a SELECT statement that returns "single rows" : "INTO" clause
 
 
select COUNT(*) from xx_emp
 
DECLARE
V_COUNT NUMBER;
V_SQL VARCHAR2(500);
V_DEPARTMENT NUMBER := 10;
BEGIN
V_SQL := 'select COUNT(*) from xx_emp';
Execute Immediate V_SQL INTO V_COUNT ;
dbms_output.put_line ('Total Number of Employee is : '|| V_COUNT);
END;
 
 
OUTPUT :-  Total Number of Employee is : 15
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
SELECT ENAME, EMPNO, SAL FROM XX_EMP WHERE EMPNO=9001
 
DECLARE
V_SQL VARCHAR2(500);
 
V_EMP_NAME VARCHAR2(50);
V_EMP_NO NUMBER;
V_SALARY NUMBER;
 
BEGIN
 
V_SQL := 'SELECT ENAME, EMPNO, SAL FROM XX_EMP WHERE EMPNO=9001';
EXECUTE IMMEDIATE V_SQL INTO V_EMP_NAME,V_EMP_NO,V_SALARY;
 
Dbms_output.put_line('Employee Name:    '||V_EMP_NAME);
Dbms_output.put_line('Employee Number:  '||V_EMP_NO);
Dbms_output.put_line('Salary:  '||V_SALARY);
END;
 
 
OUTPUT :-
Employee Name:    Manoj
Employee Number:  9001
Salary:  1000
 
 
--==========================================================================================
• If the dynamic SQL statement is a SELECT statement that returns "single rows" : "INTO" clause
with binbind variables in the "USING" clause.
--==========================================================================================
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Single Bind Variable with Execute Immediate of Dynamic SQL
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
select * from xx_emp
 
DECLARE
V_COUNT NUMBER;
V_SQL VARCHAR2(500);
V_DEPARTMENT NUMBER := 10;
BEGIN
 
V_SQL := 'select COUNT(*) from xx_emp where deptno = :V_DEPT_NO';
 
Execute Immediate V_SQL INTO V_COUNT USING V_DEPARTMENT ;
 
dbms_output.put_line ('Total Number of Employee in Department '||V_DEPARTMENT||' is : '|| V_COUNT);
END;
 
OUTPUT :- Total Number of Employee in Department 10 is : 3
 
 
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Multiple Bind Variable with Execute Immediate of Dynamic SQL
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
select * from xx_emp where empno = '9001'
 
 
DECLARE
V_SQL VARCHAR2(500);
V_EMP_NAME VARCHAR2(50);
V_EMP_NO NUMBER;
V_SALARY NUMBER;
 
BEGIN
 
V_SQL := 'SELECT ENAME, EMPNO, SAL FROM XX_EMP WHERE EMPNO =:EMPLOYEE_NUMBER AND SAL > :SALARY';
 
EXECUTE IMMEDIATE V_SQL INTO V_EMP_NAME,V_EMP_NO,V_SALARY USING 9001, 500;
 
Dbms_output.put_line('Employee Name:    '||V_EMP_NAME);
Dbms_output.put_line('Employee Number:  '||V_EMP_NO);
Dbms_output.put_line('Salary:  '||V_SALARY);
 
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line ('No Record found with above conditions');
END;
 
OUTPUT :-
Employee Name:    Manoj
Employee Number:  9001
Salary:  1000
 
x