Mega Code Archive

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>