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') |
