Dynamic SQL in Oracle (With Example)

Dynamic SQL in Oracle is the run-time generation of Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL) statements. It differs from static SQL in a way that it does not have a fixed set of clauses as SQL commands. The objects are created and updated in Dynamic SQL through the normal string. Data are inserted, modified or deleted in the same way as well. Dynamic SQL is also used to generate PLSQL blocks at run-time. This process to generate Dynamic SQL through PLSQL code at run-time is called Dynamic SQL Processing Technique.

Advantages of Dynamic SQL

  • When a developer is unaware of SQL parts such as table name, column name, number of parameters etc., Dynamic SQL is used.
  • Write ad-hoc programs to perform some task.
  • To process Data Definition Language (DDL) and Data Control Language (DCL) in PLSQL code.
  • The requirement to create objects dynamically using different names

Example

In SQL form

SPOOL /home/scripts/drop.sql
SELECT
'drop table '||tablename||';'
FROM user_tables;
SPOOL OFF;

@/home/scripts/drop.sql

In PLSQL Form

DECLARE
  v_str VARCHAR2(200);
BEGIN
  FOR rec IN (SELECT * FROM user_tables) LOOP
    v_str := 'drop table ' || rec.table_name;
    DBMS_OUTPUT.PUT_LINE(v_str);
    EXECUTE IMMEDIATE v_str;
  END LOOP;
END;

Two Types of Dynamic SQL

You can execute dynamic SQL in Oracle using two techniques

  • Native Dynamic SQL
  • DBMS_SQL API

Native Dynamic SQL

The native dynamic SQL is integrated with SQL so it is easily understandable and readable. It is much faster than DBMS_SQL API.

Example

CREATE PROCEDURE insert_into_table(table_name VARCHAR2,
                                   empnumber NUMBER,
                                   empname VARCHAR2) IS
  v_str VARCHAR2(200);
BEGIN
  v_str := 'INSERT INTO ' || table_name || ' VALUES(:empno, :ename)';
  EXECUTE IMMEDIATE v_str USING empnumber, empname;
END;

Drawback of Native Dynamic SQL

  • The queries that return multiple rows cannot be used in Native Dynamic SQL.
  • Native Dynamic SQL does not allow binding by name. It allows binding by position

Execute Immediate

Execute Immediate executes SQL statements and anonymous PLSQL blocks.

Example

DECLARE
  ls_sql_statement VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE employees (empid NUMBER, empname VARCHAR2(100)';
  ls_sql_statement := 'INSERT INTO employees VALUES (:1, :2)';
  EXECUTE IMMEDIATE ls_sql_statement using ln_empid, ls_empname;
END;

DBMS_SQL

DBMS_SQL is a library which requires procedures and functions to be executed in strict sequence to execute a Dynamic SQL. It allows binding by name. The best example is Oracle Apex.

Example

CREATE PROCEDURE insert_into_table(table_name VARCHAR2,
                                   empnumber  NUMBER,
                                   empname    VARCHAR2) IS
  cur_tab        INTEGER;
  v_str          VARCHAR2(200);
  rows_processed BINARY_INTEGER;
BEGIN
  v_str := 'INSERT INTO ' || table_name || ' VALUES (:empno, :ename)';
  
-- open cursor
  cur_tab := dbms_sql.open_cursor;

  -- parse cursor
  dbms_sql.parse(cur_tab, v_str, dbms_sql.native);
  -- supply binds
  dbms_sql.bind_variable(cur_tab, ':empno', empnumber);
  dbms_sql.bind_variable(cur_tab, ':ename', empname);

  -- execute cursor
  rows_processed := dbms_sql.execute(cur_tab);

  -- close cursor
  dbms_sql.close_cursor(cur_tab);

END;

Drawback of DBMS_SQL

  • DBMS_SQL is slower than Native Dynamic SQL

Hope you like this article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.