Call a stored procedure in dynamic script : Execute Immediate « PL SQL « Oracle PL / 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>

   
  








Related examples in the same category

1.EXECUTE IMMEDIATE commands
2.Execute immediate for an insert statement
3.Use EXECUTE IMMEDIATE to call procedure and save the returning value
4.Use execute immediate to insert random numbers
5.execute immediate with 'insert ... using' to pass variable into insert statement
6.Timing Bind variable
7.Call 'execute immediate' to drop table, create table and insert data
8.EXECUTE IMMEDIATE
9.Use parameters in EXECUTE IMMEDIATE
10.Pass value out of dynamic select statement
11.Build up the CREATE TABLE statement and run it with EXECUTE IMMEDIATE
12.Use USING clause with EXECUTE IMMEDIATE to handle bind variables.
13.Use of EXECUTE IMMEDIATE for single-row queries.
14.Create Dynamic Tables
15.demonstrates the effect of duplicate placeholders with EXECUTE IMMEDIATE.
16.how to pass a NULL value to EXECUTE IMMEDIATE.
17.Use native dynamic SQL to re-create MyTable.
18.Use DBMS_SQL to re-create MyTable.
19.execute immediate using variable
20.Create dynamic sql statement and get the result
21.Dynamically create packages
22.Use execute immediate to create dynamic query in a procedure
23.Performance between using statement and string concatenation for dynamic sql