Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

13-5.	Enhance your table count function (see the “Beginner” section) that returns the number of rows in the specified table to add an optional WHERE clause. The user should not have to include the WHERE keyword, but if he does, the function interprets and constructs the request properly.
13-6.	Write a procedure that drops whatever object you specify (table, view, object type, etc.).
13-7.	Why can’t the dropit procedure be implemented as follows? CREATE OR REPLACE PROCEDURE dropit ( ittype IN VARCHAR2, itname IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'drop :type :name' USING ittype, itname; END; /
13-8.	Build a generic procedure to execute any DDL statement. Specify that this program “run as invoker” and not as the definer.
13-9.	Use your solution to 13-8 to create a procedure that creates an index of the specified name for the specified table and columns.
13-10.	In Illinois, 11 men have been released from Death Row (as of March 1999) after having finally been proven innocent (one man spent over 17 years there). Which of the following two programs that remove innocent men from Death Row will run more efficiently? Why? CREATE OR REPLACE PROCEDURE release_innocents_in_illinois IS TYPE names_t IS TABLE OF VARCHAR2(100); innocent names_t := names_t ( 'WILLIAMS', 'JIMMERSON', 'LAWSON', 'GAUGER', 'BURROWS', 'CRUZ', 'TILLIS', 'COBB', 'HERNANDEZ', 'PORTER', 'SMITH'); BEGIN FOR indx IN innocent.FIRST .. innocent.LAST LOOP EXECUTE IMMEDIATE 'DELETE FROM death_row WHERE name = ' || innocent(indx); ...