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
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.
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 executes SQL statements and anonymous PLSQL blocks.
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 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.
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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!