Let us learn how to write Dynamic SQL Queries in PL/SQL using Native Dynamic SQL(NDS)

Introduction

If we need to build and execute SQL statements at runtime , we need to go for Dymanic SQL. In this article we will look into how to do so using NDS which has two constructs

1. Execute Immediate
2. Open <Cursor Name > For

1.Execute Immediate

It is use for executing

1. Single Row Query
2. Multiple Row Query

(a)Execute Immediate as Single Row Query

Declare 
	V_Name varchar2(100);	
	V_Query varchar2(1000);
Begin	
	V_Query := 'SELECT ENAME FROM EMP WHERE EMPNO = 7698';
	Execute Immediate V_Query Into  V_Name;
	DBMS_OUTPUT.PUT_LINE('The Employee Name is : ' || V_Name || ' for Employee No : 7698');
Exception 
	When Others then
	DBMS_OUTPUT.PUT_LINE('Error is ' || sqlerrm);
End;
/

Save it as Prog2.sql.Running the program yields the below

SQL>  @Prog2
The Employee Name is : BLAKE for Employee No : 7698

In this case, the Ename for EmpNo = 7698 is store into the V_Name variable and then is outputted to the result pane.

(b)Execute Immediate as Multiple Row Query

DECLARE 
TYPE Emp_Rec_Type IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
V_Emp_Rec_Tab Emp_Rec_Type;
V_Query VARCHAR2(1000); 
BEGIN
  V_Query := 'SELECT EMPNO,ENAME FROM EMP';
  Execute Immediate V_Query  
  BULK COLLECT INTO V_Emp_Rec_Tab;
  FOR I IN 1..V_Emp_Rec_Tab.COUNT
   LOOP     
      DBMS_OUTPUT.PUT_LINE('Emp No is : ' || V_Emp_Rec_Tab(I).EMPNO || ' and Employee Name is : ' || V_Emp_Rec_Tab(I).ENAME);
    END LOOP; 
END;
/

In this case first we create a table type record for Emp table. Then we are assigning the result of Execute Immediate to V_Emp_Rec_Tab and then outputting the result by looping through it

2.Open <Cursor Name > For

It is use for multiple row query processing using dynamic SQL

DECLARE 
TYPE cur_type IS REF CURSOR;
 V_Query VARCHAR2(1000);
 V_Employee_Cursor cur_type; 
 V_Emp_No    NUMBER;
 V_Emp_Name VARCHAR2(50);

BEGIN
  -- Use concatenation to form the SELECT statement
  V_Query := 'SELECT EMPNO,ENAME FROM EMP';
  -- Open a cursor variable for the query
  OPEN V_Employee_Cursor FOR V_Query;
    -- Loop through each row to find employees who perform the specified job
    LOOP
      -- Fetch the employee name and ID into variables
      FETCH V_Employee_Cursor 
      INTO V_Emp_No,V_Emp_Name;
      EXIT WHEN V_Employee_Cursor%NOTFOUND;
      -- Process row here
      DBMS_OUTPUT.PUT_LINE('Emp No is : ' || V_Emp_No || ' and Employee Name is : ' || V_Emp_Name );
    END LOOP;
  CLOSE V_Employee_Cursor;
END;
/

It is almost similar to the previous one. Running it yields the below

SQL> @Prog3
Emp No is : 7369 and Employee Name is : SMITH
Emp No is : 7499 and Employee Name is : ALLEN
Emp No is : 7521 and Employee Name is : WARD
Emp No is : 7566 and Employee Name is : JONES
Emp No is : 7654 and Employee Name is : MARTIN
Emp No is : 7698 and Employee Name is : BLAKE
Emp No is : 7782 and Employee Name is : CLARK
Emp No is : 7788 and Employee Name is : SCOTT
Emp No is : 7839 and Employee Name is : KING
Emp No is : 7844 and Employee Name is : TURNER
Emp No is : 7876 and Employee Name is : ADAMS
Emp No is : 7900 and Employee Name is : JAMES
Emp No is : 7902 and Employee Name is : FORD
Emp No is : 7934 and Employee Name is : MILLER

PL/SQL procedure successfully completed.

Conclusion

So in this article, we have seen how to write Dynamic SQL Queries in PL/SQL using Native Dynamic SQL(NDS).Hope this will be helpful.Thanks for reading.