Oracle PL/SQL/PL SQL/Execute Immediate — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Версия 13:45, 26 мая 2010

Build up the CREATE TABLE statement and run it with EXECUTE IMMEDIATE

  
SQL>
SQL> SET ECHO ON
SQL>
SQL> CREATE OR REPLACE PROCEDURE make_mytable (
  2      username IN VARCHAR2
  3  ) AS
  4      create_stmt     VARCHAR2(200);
  5  BEGIN
  6      create_stmt := "CREATE TABLE "
  7                     || username || ".mytable"
  8                     || "(myrow NUMBER, mydesc VARCHAR2(50))";
  9
 10      EXECUTE IMMEDIATE create_stmt;
 11  END;
 12  /
Procedure created.
SQL>
SQL>
SQL> --



Call a stored procedure in dynamic script

  
SQL>
SQL> CREATE TABLE place (
  2    room_id          NUMBER(5) PRIMARY KEY,
  3    building         VARCHAR2(15),
  4    room_number      NUMBER(4),
  5    number_seats     NUMBER(4),
  6    description      VARCHAR2(50)
  7    );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
  2             VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
  2             VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
  2             VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created.
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE printLecturer
  2    (v_Numlecturer IN OUT NUMBER) AS
  3    v_LocalCount NUMBER := 0;
  4    myLecturerName VARCHAR2(100);
  5    CURSOR c_StudentNames IS
  6      SELECT first_name || " " || last_name
  7        FROM lecturer
  8        ORDER BY ID;
  9  BEGIN
 10    OPEN c_StudentNames;
 11    FOR v_Count IN 1..v_Numlecturer LOOP
 12      FETCH c_StudentNames INTO myLecturerName;
 13      IF c_StudentNames%NOTFOUND THEN
 14        EXIT;
 15      END IF;
 16
 17      DBMS_OUTPUT.PUT_LINE("  " || myLecturername);
 18      v_LocalCount := v_LocalCount + 1;
 19    END LOOP;
 20
 21    CLOSE c_StudentNames;
 22    v_Numlecturer := v_LocalCount;
 23  END printLecturer;
 24  /
Procedure created.
SQL>
SQL> DECLARE
  2    codeBlock VARCHAR2(1000);
  3
  4    v_Building place.building%TYPE;
  5    v_RoomNum  place.room_number%TYPE;
  6    v_RoomID   place.room_ID%TYPE := 20006;
  7    v_Numlecturer NUMBER;
  8  BEGIN
  9    EXECUTE IMMEDIATE
 10      "DELETE FROM place WHERE room_id = :ID RETURNING building, room_number INTO :building, :room_num"
 11      USING v_RoomID, OUT v_Building, OUT v_RoomNum;
 12
 13    DBMS_OUTPUT.PUT_LINE("Deleted Room " || v_RoomNum || " in " || v_Building);
 14
 15    codeBlock := "BEGIN printLecturer(:num); END;";
 16
 17    v_Numlecturer := 4;
 18    EXECUTE IMMEDIATE codeBlock USING IN OUT v_Numlecturer;
 19    DBMS_OUTPUT.PUT_LINE("Returned value is " || v_Numlecturer);
 20
 21    v_Numlecturer := 20;
 22    EXECUTE IMMEDIATE codeBlock USING IN OUT v_Numlecturer;
 23    DBMS_OUTPUT.PUT_LINE("Returned value is " || v_Numlecturer);
 24  END;
 25  /
Deleted Room 100 in Music Building
  Scott Lawson
  Mar Wells
  Jone Bliss
  Man Kyte
Returned value is 4
  Scott Lawson
  Mar Wells
  Jone Bliss
  Man Kyte
  Pat Poll
  Tim Viper
  Barbara Blues
  David Large
  Chris Elegant
  Rose Bond
  Rita Johnson
  Sharon Clear
Returned value is 12
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
SQL>
SQL>



Call "execute immediate" to drop table, create table and insert data

  
SQL>
SQL> set echo on
SQL> set serveroutput on
SQL> begin
  2     for i in 1 .. 100 loop
  3           begin
  4                execute immediate "drop table t"||i;
  5           exception
  6                when others then null;
  7           end;
  8           execute immediate "create table t"||i||" ( dummy char(1) )";
  9           execute immediate "insert into t"||i||" values ( ""x"" )";
 10     end loop;
 11  end;
 12  /
PL/SQL procedure successfully completed.
SQL>



Create dynamic sql statement and get the result

    
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace function get_value_dyn( p_empno in number, p_cname in varchar2 ) return varchar2
  2  as
  3      l_value  varchar2(4000);
  4  begin
  5      execute immediate "select " || p_cname || " from emp where empno = :x"
  6      into l_value
  7      using p_empno;
  8      return l_value;
  9  end;
 10  /
Function created.
SQL>
SQL>
SQL> declare
  2          l_dummy varchar2(30);
  3  begin
  4      for i in 1 .. 500
  5      loop
  6          for x in ( select empno from emp )
  7          loop
  8              l_dummy := get_value_dyn(x.empno, "ENAME" );
  9              l_dummy := get_value_dyn(x.empno, "EMPNO" );
 10              l_dummy := get_value_dyn(x.empno, "HIREDATE" );
 11          end loop;
 12      end loop;
 13  end;
 14  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>



Create Dynamic Tables

  
CREATE OR REPLACE PROCEDURE CreateDynamicTables(
  p_Method IN VARCHAR2) AS
  v_CreateString1 VARCHAR2(100) := "CREATE TABLE dbms_sql_table (f1 NUMBER)";
  v_CreateString2 VARCHAR2(100) := "CREATE TABLE native_table (f1 NUMBER)";
  v_Dummy INTEGER;
  v_CursorID INTEGER;
BEGIN
  IF p_Method = "DBMS_SQL" THEN
    v_CursorID := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_CursorID, v_CreateString1, DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
  ELSE
    EXECUTE IMMEDIATE v_CreateString2;
  END IF;
END CreateDynamicTables;
/
exec CreateDynamicTables("DBMS_SQL")
exec CreateDynamicTables("native")
DROP TABLE dbms_sql_table;
DROP TABLE native_table;

--



demonstrates the effect of duplicate placeholders with EXECUTE IMMEDIATE.

  
SQL>
SQL>
SQL> CREATE TABLE duplicates (
  2    f1 NUMBER,
  3    f2 NUMBER,
  4    f3 NUMBER,
  5    f4 NUMBER);
Table created.
SQL>
SQL> DECLARE
  2    v_InsertStmt VARCHAR2(100);
  3    v_f1 NUMBER := 1;
  4    v_f2 NUMBER := 2;
  5    v_f3 NUMBER := 3;
  6  BEGIN
  7    v_InsertStmt :=
  8      "INSERT INTO duplicates VALUES (:a, :b, :c, :a)";
  9
 10    EXECUTE IMMEDIATE v_InsertStmt
 11      USING v_f1, v_f2, v_f3, v_f1;
 12  END;
 13  /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
  2    FROM duplicates;
        F1         F2         F3         F4
---------- ---------- ---------- ----------
         1          2          3          1
1 row selected.
SQL>
SQL> DECLARE
  2    v_InsertStmt VARCHAR2(100);
  3    v_f1 NUMBER := 4;
  4    v_f2 NUMBER := 5;
  5    v_f3 NUMBER := 6;
  6  BEGIN
  7    v_InsertStmt :=
  8      "BEGIN " ||
  9      "  INSERT INTO duplicates VALUES (:a, :b, :c, :a); " ||
 10      "END;";
 11
 12    EXECUTE IMMEDIATE v_InsertStmt
 13      USING v_f1, v_f2, v_f3;
 14  END;
 15  /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
  2    FROM duplicates;
        F1         F2         F3         F4
---------- ---------- ---------- ----------
         1          2          3          1
         4          5          6          4
2 rows selected.
SQL>
SQL> DROP TABLE duplicates;
Table dropped.
SQL>
SQL>



Dynamically create packages

    
SQL> declare
  2   x varchar2(32767);
  3  begin
  4   for i in 1 .. 300 loop
  5     x := x || " procedure X"||i||";";
  6   end loop;
  7   execute immediate "create or replace package aTest is "||x||" end;";
  8   x := replace(x,";"," is y number; begin y := 1; end;");
  9
 10   execute immediate "create or replace package body aTest is "||x||" end;";
 11  end;
 12  /
PL/SQL procedure successfully completed.
SQL>



EXECUTE IMMEDIATE

  
SQL>
SQL> SET ECHO ON
SQL>
SQL> BEGIN
  2      EXECUTE IMMEDIATE "CREATE TABLE YOURTABLE ("
  3                        || "YOURROW NUMBER, "
  4                        || "YOURDESC VARCHAR2(50))";
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table yourtable;
Table dropped.
SQL> --



EXECUTE IMMEDIATE commands

  
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    sqlString  VARCHAR2(200);
  3    codeBlock VARCHAR2(200);
  4  BEGIN
  5    EXECUTE IMMEDIATE "CREATE TABLE execute_table (col1 VARCHAR(10))";
  6
  7    FOR v_Counter IN 1..10 LOOP
  8      sqlString :=
  9        "INSERT INTO execute_table
 10           VALUES (""Row " || v_Counter || """)";
 11      EXECUTE IMMEDIATE sqlString;
 12    END LOOP;
 13
 14    codeBlock :=
 15      "BEGIN
 16         FOR v_Rec IN (SELECT * FROM execute_table) LOOP
 17           DBMS_OUTPUT.PUT_LINE(v_Rec.col1);
 18         END LOOP;
 19       END;";
 20
 21    EXECUTE IMMEDIATE codeBlock;
 22
 23    EXECUTE IMMEDIATE "DROP TABLE execute_table";
 24  END;
 25  /
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9
Row 10
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>



Execute immediate for an insert statement

 

SQL>
SQL> -- Define procedure and execute them
SQL>
SQL> create table mytable ( x int );
Table created.
SQL>
SQL> create or replace procedure myProcecdure1 as
  2  begin
  3      for i in 1 .. 10000
  4      loop
  5          execute immediate "insert into mytable values ( :x )" using i;
  6      end loop;
  7  end;
  8  /
Procedure created.
SQL>
SQL> create or replace procedure myProcecdure2 as
  2  begin
  3      for i in 1 .. 10000
  4      loop
  5          execute immediate "insert into mytable values ( "||i||")";
  6      end loop;
  7  end;
  8  /
Procedure created.
SQL>
SQL> exec myProcecdure1;
PL/SQL procedure successfully completed.
SQL>
SQL> exec myProcecdure2;
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>



execute immediate using variable

    
SQL>
SQL>
SQL> create table t ( x int );
Table created.
SQL> create or replace procedure proc1
  2  as
  3  begin
  4      for i in 1 .. 10000
  5      loop
  6          execute immediate "insert into t values ( :x )" using i;
  7      end loop;
  8  end;
  9  /
Procedure created.
SQL> create or replace procedure proc2
  2  as
  3  begin
  4      for i in 1 .. 10000
  5      loop
  6          execute immediate "insert into t values ( "||i||")";
  7      end loop;
  8  end;
  9  /
Procedure created.
SQL> exec proc1
PL/SQL procedure successfully completed.
SQL> exec proc2
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.



execute immediate with "insert ... using" to pass variable into insert statement

  
SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL> set echo on
SQL>
SQL>      declare
  2           myNumber number;
  3       begin
  4           for i in 1 .. 10
  5           loop
  6               myNumber := dbms_random.random;
  7
  8               execute immediate
  9               "insert into t values ( :x1, :x2, :x3, :x4 )"
 10                       using myNumber, myNumber, myNumber, myNumber;
 11           end loop;
 12           commit;
 13       end;
 14  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
        C1         C2         C3         C4
---------- ---------- ---------- ----------
-215681493 -215681493 -215681493 -215681493
  65675438   65675438   65675438   65675438
-544483009 -544483009 -544483009 -544483009
 327912408  327912408  327912408  327912408
-233632941 -233632941 -233632941 -233632941
 623357100  623357100  623357100  623357100
 616373886  616373886  616373886  616373886
-703556128 -703556128 -703556128 -703556128
-928105331 -928105331 -928105331 -928105331
-179825426 -179825426 -179825426 -179825426
10 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --



how to pass a NULL value to EXECUTE IMMEDIATE.

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> DECLARE
  2    sqlString VARCHAR2(1000);
  3    v_NullVar VARCHAR2(10);
  4  BEGIN
  5    sqlString :=
  6      "INSERT INTO MyTable (num_col, char_col) VALUES
  7         (:n, :c)";
  8
  9    EXECUTE IMMEDIATE sqlString USING 1, v_NulLVar;
 10  END;
 11  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>



Pass value out of dynamic select statement

  
SQL>
SQL> CREATE TABLE MyTable(yourRow INTEGER, yourDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2      block_to_execute VARCHAR2(200) :=
  3          "BEGIN
  4              SELECT YourRow,YourDesc
  5              INTO :1, :2 FROM myTable
  6              WHERE YourRow = 2;
  7           END;";
  8
  9      YourRow NUMBER;
 10      YourDesc VARCHAR2(100);
 11  BEGIN
 12      EXECUTE IMMEDIATE block_to_execute USING OUT YourRow, OUT YourDesc;
 13      DBMS_OUTPUT.PUT_LINE(YourRow || " " || YourDesc);
 14  END;
 15  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at line 2
ORA-06512: at line 12

SQL>
SQL> drop table mytable;
Table dropped.
SQL> --



Performance between using statement and string concatenation for dynamic sql

    
SQL>
SQL> create or replace procedure DO_WORK(x number) is
  2   y number;
  3   begin
  4       y := x;
  5   end;
  6  /
Procedure created.
SQL> set timing on
SQL>
SQL> begin
  2       for i in 1 .. 100 loop
  3           execute immediate "begin do_work(:x); end;" using i;
  4       end loop;
  5   end;
  6  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL>
SQL> begin
  2       for i in 1 .. 100 loop
  3           execute immediate "begin do_work("||i||"); end;";
  4       end loop;
  5   end;
  6  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17



Timing Bind variable

  
SQL>
SQL>  alter system flush shared_pool;
System altered.
SQL>      set serveroutput on
SQL>      declare
  2          l_start number;
  3          l_cnt   number;
  4      begin
  5          l_start := dbms_utility.get_time;
  6
  7          for i in 1 .. 100
  8          loop
  9              execute immediate
 10             "select count(*) from dual where dummy = """ || to_char(i) || """" INTO l_cnt;
 11         end loop;
 12
 13         dbms_output.put_line( "No Binds " || (dbms_utility.get_time-l_start) || " hsecs" );
 14         l_start := dbms_utility.get_time;
 15         for i in 1 .. 100
 16         loop
 17             execute immediate "select count(*) from dual where dummy = :x"
 18             INTO l_cnt
 19             USING to_char(i);
 20         end loop;
 21         dbms_output.put_line( "Binding " || (dbms_utility.get_time-l_start) || " hsecs" );
 22     end;
 23     /
No Binds 7 hsecs
Binding 1 hsecs
PL/SQL procedure successfully completed.
SQL>



Use DBMS_SQL to re-create MyTable.

  
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
  2    p_Description IN VARCHAR2) IS
  3
  4    v_Cursor        NUMBER;
  5    v_CreateString  VARCHAR2(100);
  6    v_DropString    VARCHAR2(100);
  7  BEGIN
  8    v_Cursor := DBMS_SQL.OPEN_CURSOR;
  9
 10    v_DropString := "DROP TABLE MyTable";
 11
 12    BEGIN
 13      DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.NATIVE);
 14    EXCEPTION
 15      WHEN OTHERS THEN
 16        IF SQLCODE != -942 THEN
 17          RAISE;
 18        END IF;
 19    END;
 20
 21    v_CreateString := "CREATE TABLE MyTable " || p_Description;
 22    DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.NATIVE);
 23
 24    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      DBMS_SQL.CLOSE_CURSOR(v_Cursor);
 28      RAISE;
 29  END RecreateTempTable;
 30  /
Procedure created.
SQL> drop table MyTable;
Table dropped.
SQL>



Use EXECUTE IMMEDIATE to call procedure and save the returning value

  
SQL>
SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
  4  END CallProc1;
  5  /
Procedure created.
SQL>
SQL> DECLARE
  2    myResult VARCHAR2(50);
  3  BEGIN
  4    EXECUTE IMMEDIATE "CALL CallProc1(""Hello from PL/SQL"")";
  5    EXECUTE IMMEDIATE "CALL CallFunc(""Hello from PL/SQL"") INTO :myResult"
  6    USING OUT myResult;
  7  END;
  8  /
CallProc1 called with Hello from PL/SQL
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>



Use execute immediate to create dynamic query in a procedure

    
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.

SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.

SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.

SQL>
SQL>
SQL> CREATE or replace PROCEDURE update_sal (id in number, p_sal in number)
  2    authid current_user
  3    AS
  4    BEGIN
  5      update EMP set sal = p_sal where empno = id;
  6    END;
  7    /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE procedure update_sal (id in number, p_sal in number) authid current_user as
  2  begin
  3    execute immediate "update EMP set "||"sal = :x " ||"where empno = :y" using p_sal, id;
  4  end;
  5  /
Procedure created.
SQL>
SQL> exec update_sal (6,5000);
PL/SQL procedure successfully completed.
SQL>
SQL> select ename, empno, sal from EMP where ename = USER;
no rows selected
SQL>
SQL> drop procedure update_sal;
Procedure dropped.
SQL> drop table emp;
Table dropped.



Use execute immediate to insert random numbers

  
SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL>
SQL> declare
  2      myNumber number;
  3  begin
  4      for i in 1 .. 10
  5      loop
  6          myNumber := dbms_random.random;
  7
  8          execute immediate
  9          "insert into t values ( " || myNumber || "," ||
 10                                       myNumber || "," ||
 11                                       myNumber || "," ||
 12                                       myNumber || ")";
 13      end loop;
 14      commit;
 15  end;
 16  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from t;
        C1         C2         C3         C4
---------- ---------- ---------- ----------
-934884549 -934884549 -934884549 -934884549
1258621996 1258621996 1258621996 1258621996
-739854461 -739854461 -739854461 -739854461
-2.077E+09 -2.077E+09 -2.077E+09 -2.077E+09
 286830488  286830488  286830488  286830488
-1.128E+09 -1.128E+09 -1.128E+09 -1.128E+09
1345837309 1345837309 1345837309 1345837309
  87152190   87152190   87152190   87152190
 561507416  561507416  561507416  561507416
-2.139E+09 -2.139E+09 -2.139E+09 -2.139E+09
10 rows selected.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --



Use native dynamic SQL to re-create MyTable.

  
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE RecreateTempTable (
  2    p_Description IN VARCHAR2) IS
  3
  4    v_CreateString  VARCHAR2(100);
  5    v_DropString    VARCHAR2(100);
  6  BEGIN
  7    v_DropString := "DROP TABLE MyTable";
  8
  9    BEGIN
 10      EXECUTE IMMEDIATE v_DropString;
 11    EXCEPTION
 12      WHEN OTHERS THEN
 13        IF SQLCODE != -942 THEN
 14          RAISE;
 15        END IF;
 16    END;
 17
 18    v_CreateString := "CREATE TABLE MyTable " || p_Description;
 19    EXECUTE IMMEDIATE v_CreateString;
 20  END RecreateTempTable;
 21  /
Procedure created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
SQL>



Use of EXECUTE IMMEDIATE for single-row queries.

  
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL> DECLARE
  2    v_SQLQuery VARCHAR2(200);
  3    v_Class session%ROWTYPE;
  4    v_Description session.description%TYPE;
  5  BEGIN
  6    v_SQLQuery :=
  7      "SELECT description " ||
  8      "  FROM session " ||
  9      "  WHERE department = ""ECN""" ||
 10      "  AND course = 203";
 11
 12    EXECUTE IMMEDIATE v_SQLQuery
 13      INTO v_Description;
 14
 15    DBMS_OUTPUT.PUT_LINE("Fetched " || v_Description);
 16
 17    v_SQLQuery :=
 18      "SELECT * " ||
 19      "  FROM session " ||
 20      "  WHERE description = :description";
 21    EXECUTE IMMEDIATE v_SQLQuery
 22      INTO v_Class
 23      USING v_Description;
 24
 25    DBMS_OUTPUT.PUT_LINE(
 26      "Fetched " || v_Class.department || " " || v_Class.course);
 27
 28    v_SQLQuery := "SELECT * FROM session";
 29    EXECUTE IMMEDIATE v_SQLQuery
 30      INTO v_Class;
 31  END;
 32  /
Fetched Economics 203
Fetched ECN 203
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 29

SQL>
SQL> drop table session;
Table dropped.
SQL>



Use parameters in EXECUTE IMMEDIATE

  
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
  2      v_YourNum   NUMBER;
  3      v_YourDesc  VARCHAR2(50);
  4      v_INSERT_stmt VARCHAR2(100);
  5  BEGIN
  6      v_INSERT_stmt := "INSERT INTO mytable VALUES (:1, :2)";
  7
  8      v_YourNum := 1;
  9      v_YourDesc := "One";
 10      EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
 11
 12      v_YourNum := 2;
 13      v_YourDesc := "Two";
 14      EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
 15
 16      v_YourNum := 3;
 17      v_YourDesc := "Three";
 18      EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
 19
 20      v_YourNum := 4;
 21      v_YourDesc := "Four";
 22      EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
 23
 24      v_YourNum := 5;
 25      v_YourDesc := "Five";
 26      EXECUTE IMMEDIATE v_INSERT_stmt USING v_YourNum, v_YourDesc;
 27  END;
 28  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * FROM MYTABLE
  2
SQL> drop table mytable;
Table dropped.
SQL> --



Use USING clause with EXECUTE IMMEDIATE to handle bind variables.

  
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5) NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3) NOT NULL,
  5    grade      CHAR(1)
  6    );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10004, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10005, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10006, "HIS", 101, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10007, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10008, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10009, "HIS", 101, "D");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10010, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10008, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10010, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10009, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10006, "MUS", 410, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10011, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, "MUS", 410, "B");
1 row created.
SQL>
SQL>
SQL> DECLARE
  2    sqlString  VARCHAR2(1000);
  3    codeBlock VARCHAR2(1000);
  4
  5    CURSOR c_EconMajor IS
  6      SELECT *
  7        FROM lecturer
  8        WHERE major = "Economics";
  9  BEGIN
 10    sqlString :=
 11      "INSERT INTO session (department, course, description,
 12                            max_lecturer, current_lecturer,
 13                            num_credits)
 14         VALUES (:dep, :course, :descr, :max_s, :cur_s, :num_c)";
 15
 16    EXECUTE IMMEDIATE sqlString USING
 17      "ECN", 103, "Economics 103", 10, 0, 3;
 18
 19    FOR myLecturerRec IN c_EconMajor LOOP
 20      EXECUTE IMMEDIATE
 21          "INSERT INTO myStudent
 22             (student_ID, department, course, grade)
 23           VALUES (:id, :dep, :course, NULL)"
 24        USING myLecturerRec.ID, "ECN", 103;
 25
 26      codeBlock :=
 27        "BEGIN
 28           UPDATE session SET current_lecturer = current_lecturer + 1
 29           WHERE department = :d and course = :c;
 30         END;";
 31
 32      EXECUTE IMMEDIATE codeBlock USING "ECN", 103;
 33    END LOOP;
 34  END;
 35  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.