Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL

By : Richard Peterson Richard Peterson
Hours Updated

What is Dynamic SQL?

Dynamic SQL is a programming methodology for generating and running statements at run-time. It is mainly used to write the general-purpose and flexible programs where the SQL statements will be created and executed at run-time based on the requirement.

Ways to write dynamic SQL

PL/SQL provides two ways to write dynamic SQL

  1. NDS – Native Dynamic SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Execute Immediate

Native Dynamic SQL is the easier way to write dynamic SQL. It uses the ‘EXECUTE IMMEDIATE’ command to create and execute the SQL at run-time. But to use this way, the datatype and number of variable that to be used at a run time need to be known before. It also gives better performance and less complexity when compares to DBMS_SQL.

Syntax 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • The above syntax shows EXECUTE IMMEDIATE command.
  • Clause INTO is optional and used only if the dynamic SQL contains a select statement that fetches values. The variable type should match with the variable type of the select statement.
  • Clause USING is optional and used only if the dynamic SQL contains any bind variable.

Example 1: In this example, we are going to fetch the data from emp table for emp_no ‘1001’ using NDS statement.

NDS - Execute Immediate

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_manager
USING 1001;
Dbms_output.put_line('Employee Name:‘||lv_emp_name);
Dbms_output.put_line('Employee Number:‘||ln_emp_no);
Dbms_output.put_line(‘Salary:'||ln_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

Output

Employee Name : XXX 
Employee Number: 1001 
Salary: 15000 
Manager ED: 1000

Code Explanation:

  • Code line 2-6: Declaring variables.
  • Code line 8: Framing the SQL at run-time. SQL contains the bind variable in where condition ‘:empno’.
  • Code line 9: Executing the framed SQL text (which is done in code line 8) using the NDS command ‘EXECUTE IMMEDIATE’
  • The variables in ‘INTO’ clause (lv_emp_name, ln_emp_no, ln_salary, ln_manager) is used to hold the fetched values from the SQL query (emp_name, emp_no, salary, manager)
  • ‘USING’ clause gives the values to the bind variable in the SQL query (:emp_no).
  • Code line 10-13: Displaying the fetched values.

DBMS_SQL for Dynamic SQL

PL/SQL provide the DBMS_SQL package that allows you to work with dynamic SQL. The process of creating and executing the dynamic SQL contains the following process.

  • OPEN CURSOR: The dynamic SQL will execute in the same way as a cursor. So in order to execute the SQL statement, we must open the cursor.
  • PARSE SQL: The next step is to parse the dynamic SQL. This process will just check the syntax and keep the query ready to execute.
  • BIND VARIABLE Values: The next step is to assign the values for bind variables if any.
  • DEFINE COLUMN: The next step is to define the column using their relative positions in the select statement.
  • EXECUTE: The next step is to execute the parsed query.
  • FETCH VALUES: The next step is to fetch the executed values.
  • CLOSE CURSOR: Once the results are fetched, the cursor should be closed.

Example 1: In this example, we are going to fetch the data from emp table for emp_no ‘1001’ using DBMS_SQL statement.

DBMS_SQL for Dynamic SQL

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50);
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
ln_cursor_id NUMBER;
ln_rows_processed;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,3,ln_salary);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,4,ln_manager);

ln_rows__processed:=DBMS_SQL.EXECUTE(ln_cursor_id);

DBMS_SQL for Dynamic SQL

LOOP
IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0
THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,lv_emp_name); 
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,ln_emp_no);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||ln_emp_no); 
Dbms_output.put_line(‘Salary:‘||ln_salary); 
Dbms_output.put_line('Manager ID :‘| ln_manager);
END IF;
END LOOP;

DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id);

END:
/

Output

Employee Name:XXX 
Employee Number:1001 
Salary:15000 
Manager ID:1000

Code Explanation:

  • Code line 1-9: Variable declaration.
  • Code line 10: Framing the SQL statement.
  • Code line 11: Opening the cursor using DBMS_SQL.OPEN_CURSOR. It will return the cursor id which is opened.
  • Code line 12: After the cursor is opened, the SQL is parsed.
  • Code line 13: Bind variable ‘1001’ is assigning to the cursor id instead ‘:empno’.
  • Code line 14-17: Defining the column name based on their relative position in the SQL statement. In our case, the relative position is (1) emp_name, (2) emp_no (3) salary (4) manager. So based on this position we are defining the target variable.
  • Code line 18: Executing the query using DBMS_SQL.EXECUTE. It returns the number of records processed.
  • Code line 19-33: Fetching the records using a loop and displaying the same.
  • Code line 20: DBMS_SQL.FETCH_ROWS will fetch one record from the rows processed. It can be called repeatedly to fetch all the rows. If it cannot fetch rows, it will return 0, thus exiting the loop.

Summary

In this section, we have discussed dynamic SQL and the ways to execute DYNAMIC SQL. We have also seen the different steps in executing the dynamic SQL in both the ways. We have also seen the examples in which the same scenario is handled in both NDS and DBMS_SQL ways to perform execution at run-time.

You Might Like: