CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Native Dynamic SQL (NDS)
Version 11.1
 

Simple Statement
EXECUTE IMMEDIATE <sql_statement_string>;
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;
/

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>;
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;
 
Demonstrations

Create Table
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
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
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) 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
CREATE OR REPLACE PROCEDURE revmp.load_recs (
nav_lvl VARCHAR2,
esrloc VARCHAR2,
sectloc NUMBER,
ocaval VARCHAR2) 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
CREATE OR REPLACE PROCEDURE run_me(inval VARCHAR2) IS

BEGIN
  dbms_output.put_line(inval);  
END run_me;
/

CREATE OR REPLACE PROCEDURE process_line(procno VARCHAR2) 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
Bind Variables
DBMS_SQL
FORALL
Functions
Operators
Packages
Procedures
SQL Injection
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [96 users online]    © 2010 psoug.org