Oracle Native Dynamic SQL
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Note: To gain a full understanding of NDS it is critical that you read the docs and also work with the DBMS_SQL built-in package linked at page bottom.

In the mid 2000's I taught a class about NDS where I showed how essentially any statement could be made to work ... one student later proudly showed me the following:

BEGIN
  EXECUTE IMMEDIATE COMMIT;
END;
/


I recommend reading the guidance provided by Tom Kyte on the subject to understand when to use NDS and when not to. The above use is one to be avoided: Just because you "can" doesn't mean you "should."
 
Anonymous Blocks
Simple Statement EXECUTE IMMEDIATE <sql_statement_string>;
conn uwclass/uwclass@pdbdev

CREATE TABLE test (
testcol VARCHAR2(20));

desc test

INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('DEF');
INSERT INTO test VALUES ('xyz');
COMMIT;

SELECT * FROM test;

BEGIN
  TRUNCATE TABLE test;
END;
/
TRUNCATE TABLE test;
         *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.


BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE test';
END;
/

SELECT * FROM test;

INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('DEF');
INSERT INTO test VALUES ('xyz');
COMMIT;

SELECT * FROM test;

DECLARE
 x user_tables.table_name%TYPE := 'TEST';
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x;
END;
/

SELECT * FROM test;
Statement with Bind Variables and USING Clause EXECUTE IMMEDIATE <sql_statement_string_with_bind_variable>
USING <substitution_value>;
conn uwclass/uwclass@pdbdev

CREATE TABLE t (
mycol NUMBER(5));

BEGIN
  FOR i IN 1 .. 10000
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)'
    USING i;
  END LOOP;
END;
/

SELECT COUNT(*) FROM t;

SELECT * FROM t;
Statement with INTO Clause EXECUTE IMMEDIATE <sql_statement_string_with_bind_variable>
USING OUT <parameter>;
conn uwclass/uwclass@pdbdev

DECLARE
 sqlstr VARCHAR2(256);
 i      PLS_INTEGER;
BEGIN
  sqlstr := 'SELECT COUNT(*) FROM user_objects';
  EXECUTE IMMEDIATE sqlstr
  INTO i;
  dbms_output.put_line(i);
END;
/
 
Demonstration
Create Table conn uwclass/uwclass@pdbdev

DECLARE
 x VARCHAR2(200);
BEGIN
  x := 'CREATE TABLE xyz (col1 NUMBER(10),
        col2 VARCHAR2(20), col3 DATE)';
  EXECUTE IMMEDIATE x;
END;
/

desc xyz
Create Table (or) Alter Table conn uwclass/uwclass@pdbdev

DECLARE
 i PLS_INTEGER;
 x VARCHAR2(200);
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM all_tables
  WHERE table_name = 'XYZ';

  IF i = 0 THEN
    x := 'CREATE TABLE xyz(col1 NUMBER(10), col2 VARCHAR2(20), col3 DATE)';
      EXECUTE IMMEDIATE x;
  ELSE
    x := 'ALTER TABLE xyz ADD(new_col VARCHAR2(100))';
    EXECUTE IMMEDIATE x;
  END IF;
END;
/

SELECT COUNT(*)
FROM all_tables
WHERE table_name = 'XYZ';

desc xyz
Another Complex Demo with Bind variables and Using Clause conn uwclass/uwclass@pdbdev

CREATE TABLE dept_new (
department_no VARCHAR2(10),
department_name VARCHAR2(30),
location VARCHAR2(30));

CREATE OR REPLACE PROCEDURE nds_demo (
deptnum dept_new.department_no%TYPE,
deptname dept_new.department_name%TYPE,
location dept_new.location%TYPE) AUTHID CURRENT_USER IS
 stmt_str VARCHAR2(100);
BEGIN
  stmt_str := 'INSERT INTO dept_new
               VALUES(:deptno, :dname, :loc)';
  EXECUTE IMMEDIATE stmt_str USING deptnum, deptname, location;
END nds_demo;
/

desc nds_demo

exec nds_demo('100', 'Accounting', 'Los Angeles, CA')

SELECT * FROM dept_new;
 
Demonstration Of Dynamic SQL Statement Creation
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE revmp.load_recs (
 nav_lvl VARCHAR2,
 esrloc  VARCHAR2,
 sectloc NUMBER,
 ocaval  VARCHAR2)
AUTH_ID DEFINER IS
 IClause VARCHAR2(200) := 'INSERT INTO find_recs (servord_no, rec_name, rec_street, rec_city, rec_zip, assigned_to_esr)';
 SClause VARCHAR2(200) := ' SELECT DISTINCT s.servord_no, p.package_name, ';
 FClause VARCHAR2(200) := ' FROM servord_package p, service_order s, feed f';
 WClause VARCHAR2(300) := ' WHERE p.package_no = s.package_no AND s.servord_no = f.servord_no';
 OClause VARCHAR2(100) := ' ORDER BY TO_NUMBER(servord_no)';

 XString  VARCHAR2(1000);
 Xval PLS_INTEGER := 1;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE find_recs';

  IF nav_lvl = 'S' THEN
    SClause := SClause || 'p.package_street, p.package_city, p.package_zip_code,
    s.assigned_to_esr';
    WClause := WClause || '(+)';
  ELSIF nav_lvl = 'F' THEN
    SClause := SClause || 'f.feed_to_address, f.feed_to_city, ' ||
    'f.feed_to_zip_code, s.assigned_to_esr';
  ELSIF nav_lvl = 'U' THEN
    SClause := SClause || 'a.service_address, a.service_city, a.service_zip_code,
    s.assigned_to_esr';
    FClause := FClause || ', service_address a';
    WClause := WClause || ' AND s.servord_no = a.servord_no';
  END IF;

  IF esrloc IS NOT NULL THEN
    Xval := Xval + 10;
    WClause := WClause || ' AND s.esr_location = :E';
  END IF;

  IF sectloc IS NOT NULL THEN
    Xval := Xval + 100;
    WClause := WClause || ' AND f.geocode_section = :S';
  END IF;

  IF ocaval = 'O' THEN
    Xval := Xval + 1000;
    WClause := WClause || ' AND servord_compl_date IS NULL';
  ELSIF ocaval = 'C' THEN
    Xval := Xval + 1000;
    WClause := WClause || ' AND servord_compl_date IS NOT NULL';
  END IF;

  Xstring := IClause || SClause || FClause || WClause || OClause;

  IF Xval = 1 THEN
    EXECUTE IMMEDIATE Xstring;
  ELSIF Xval = 11 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc;
  ELSIF Xval = 101 THEN
    EXECUTE IMMEDIATE Xstring USING sectloc;
  ELSIF Xval = 111 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc, sectloc;
  ELSIF Xval = 1001 THEN
    EXECUTE IMMEDIATE Xstring;
  ELSIF Xval = 1011 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc;
  ELSIF Xval = 1101 THEN
    EXECUTE IMMEDIATE Xstring USING sectloc;
  ELSIF Xval = 1111 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc, sectloc;
  END IF;
  COMMIT;
END;
/
 
Using Dynamic SQL To Execute A Procedure
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE run_me(inval VARCHAR2) AUTHIC DEFINER IS
BEGIN
  dbms_output.put_line(inval);
END run_me;
/

CREATE OR REPLACE PROCEDURE process_line(procno VARCHAR2) AUTHID DEFINER IS
BEGIN
  EXECUTE IMMEDIATE 'BEGIN run_me(:b); END;'
  USING procno;
END process_line;
/

desc run_me

desc process_line

set serveroutput on

exec process_line('It Works')

Related Topics
Array Processing
Bind Variables
BULK COLLECT
DBMS_ASSERT
DBMS_SQL
FORALL
Functions
Operators
Packages
Pipelined Table Functions
Procedures
SQL Injection

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved