Oracle Native Dynamic SQL
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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_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_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;
Returning Clause conn uwclass/uwclass@pdbdev

CREATE TABLE dept_ret (
deptno   NUMBER(2),
dname    VARCHAR2(30),
location VARCHAR2(30));

CREATE SEQUENCE seq;

DECLARE
 sql_stmt VARCHAR2(128);
 dno      dept_ret.deptno%TYPE;
BEGIN
  sql_stmt := 'INSERT INTO dept_ret (deptno, dname, location) ' ||
              'VALUES (seq.NEXTVAL+1, ''PERSONNEL'', ''SEATTLE'') ' ||
              'RETURNING deptno INTO :retval';
  EXECUTE IMMEDIATE sql_stmt RETURNING INTO dno;
  dbms_output.put_line(TO_CHAR(dno));
END;
/

SELECT * FROM dept_ret;
 
Demonstration 1: 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;
/
 
Demonstration 2: 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
Database Security
DBMS_ASSERT
DBMS_SQL
FORALL
Functions
Operators
Packages
Pipelined Table Functions
Procedures
SQL Injection
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx