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

Details

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.

In this tutorial, you will learn-

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>]

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

Dynamic SQL in PL/SQL 

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:

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.

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

Dynamic SQL in PL/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);

Dynamic SQL in PL/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:

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: