Oracle PL/SQL Programming: A Developer's Workbook

Expert

15-22.	Suppose you define the following package: /* Filename on web page onecur.sql */ CREATE OR REPLACE PACKAGE onecur IS CURSOR onerow ( id_in IN employee.employee_id%TYPE) IS SELECT * FROM employee WHERE employee_id = id_in; END onecur; / You then create the following procedure, procA: CREATE OR REPLACE PROCEDURE procA IS BEGIN OPEN onecur.allrows (1005); END procA; / Next, you create the procedure procB: CREATE OR REPLACE PROCEDURE procB IS BEGIN OPEN onecur.allrows (2356); procA; END procB; / What happens when you execute procB?
15-23.	Rewrite the package in 15-22 so that it provides procedures to open and close the cursor, ensuring that a user never receives a “cursor already open” error when opening the cursor and never receives an “invalid cursor” error when closing the cursor.
15-24.	Every time you reference the USER function to retrieve the currently connected username, you do a SELECT FROM dual. On a 300-MHz laptop, 10,000 consecutive calls to USER take approximately 2.5 seconds to complete. While this is not a long time, it’s not exactly fast either. How could you modify the loop so that the USER function is called only once?
15-25.	The following package won’t compile because it’s too big: proc1 contains 20 KB of source code, and proc2 contains 22 KB. How can you redefine this code so that the call interface remains the same, but you can still call toobig.proc1 and toobig.proc2 ...