13-1.

What are the two statements added to the PL/SQL language to implement native dynamic SQL?

13-2.

Write an anonymous block that drops the employee table.

13-3.

Write a procedure that drops whichever table you pass to the procedure.

13-4.

Write a function that returns the number of rows in the specified table.

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?

13-11.

The following procedure deletes rows from any table specified by the WHERE condition:

CREATE OR REPLACE PROCEDURE delete_from (
   table_name IN VARCHAR2, condition IN VARCHAR2)
AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM ' || table_name || ' WHERE ' || condition;
END;

Change this procedure to a function that returns the number of rows that were deleted.

13-12.

To fetch a single row of information using NDS, use this statement:

EXECUTE IMMEDIATE sql_string INTO record_or_variable_list

If you want to fetch multiple rows of information, what kind of cursor construct do you need to use?

13-13.

Write a procedure to display each of the last names of employees in the employee table allowing the user to specify a dynamic WHERE clause.

13-14.

Write a generic program to execute any PL/SQL procedure with a single IN numeric argument. In other words, if you have a show_total_sales procedure that accepts as a single argument the year number, you’d call it dynamically as follows:

SQL> exec runplsql ('show_total_sales', 1998);

13-15.

Write a generic program to execute any PL/SQL procedure that has a single IN numeric argument and two VARCHAR2 OUT arguments. Suppose, for example, you have a procedure named “special_friend” that accepts the year number and returns the name of the company that gave you (a member of Congress) the largest “soft money” contribution in that year, along with name of that company’s lobbying firm. You could then call it dynamically as follows:

DECLARE
   my_pal VARCHAR2(100);
   his_lobbyist VARCHAR2(100);
BEGIN
   runplsql (
      'special_friend', 1998, my_pal, his_lobbyist);

   DBMS_OUTPUT.PUT_LINE (
      'Send ' || my_pal || ' chocolates, ' ||
      ' and set up a power lunch with ' || his_lobbyist);
END;

13-16.

Suppose you work for an insurance company. A policy has many different line items, and each line item has its own procedure for processing the data (a single IN string) and returning (through an OUT argument) a numeric value rating that information for premium pricing purposes.

The current implementation of the “master processing” procedure looks like this:

CREATE OR REPLACE PROCEDURE master_line_processor (
   line_in IN INTEGER,
   data_in IN VARCHAR2,
   rating_out OUT NUMBER)
IS
BEGIN
   IF line_in = 1
   THEN
       process_line_1 (data_in, rating_out);
   ELSIF line_in = 2
   THEN
       process_line_2 (data_in, rating_out);
   ...
   ELSIF line_in = 20456
   THEN
       process_line_20456 (data_in, rating_out);
   END IF;
END;

Unfortunately, there are lots of different line items; this procedure is long, cumbersome, and slow. It is also very hard to maintain. Rewrite the procedure using NDS to correct all of those deficiencies.

13-17.

Given the insurance company scenario from 13-16, what is the output of the following procedure if the acct_balance table has 10 entries for account 10 and 20 entries for account 20?

DECLARE
  TYPE v_curs_type IS REF CURSOR;
  v_curs            v_curs_type;
  v_balance_entries PLS_INTEGER;
  v_acct            PLS_INTEGER := 10;
BEGIN
  OPEN v_curs FOR 'SELECT COUNT(*) '     ||
                   ' FROM acct_balance ' ||
                  ' WHERE acct = :acct'
       USING v_acct;
  LOOP
    v_acct := 20;
    FETCH v_curs INTO v_balance_entries;
    EXIT WHEN v_curs%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('entries = ' || v_balance_entries);
  END LOOP;
  CLOSE v_curs;
END;

13-18.

Suppose you want to pass the same value repeatedly in a dynamic SQL statement. Here is an example of such a string:

sql_stmt := 'INSERT INTO old_growth VALUES (:x, :x, :y, :x)';

Which of the following EXECUTE IMMEDIATE statements correctly handle this situation?

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
EXECUTE IMMEDIATE sql_stmt USING a, b;

13-19.

Write a function that returns the value stored in the data element specified by name. In other words, if you have a variable last_date in the profits package, your function retrieves its value as follows:

got_it := value_in ('profits.last_date'),

13-20.

Why won’t the following use of the value_in function (see 13-19) work?

DECLARE
   n NUMBER := 100;
BEGIN
   IF value_in ('n') = 100
   THEN

13-21.

Suppose that you want to pass the same value repeatedly in a dynamic PL/SQL statement. Here is an example of such a string:

sql_stmt := 'BEGIN show_min_compensation (:x, :x, :y, :x); END;';

Which of the following EXECUTE IMMEDIATE statements correctly handles the situation in 13-20?

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
EXECUTE IMMEDIATE sql_stmt USING a, b;

13-22.

Suppose you want to pass a NULL value to a dynamic SQL statement through the USING clause. What happens when you try to execute the following statement?

BEGIN
   EXECUTE IMMEDIATE
      'UPDATE employee SET salary = :x
        WHERE fire_date IS NOT NULL' USING NULL;
END;

13-23.

How can you transform the following statement so that you can pass a NULL value for the salary?

BEGIN
   EXECUTE IMMEDIATE
      'UPDATE employee SET salary = :x
        WHERE fire_date IS NOT NULL' USING NULL;
END;

13-24.

What are all the problems with the following program, which is intended to display the names of all CEOs in the compensation table who have received large bonuses after laying off at least 1,000 of their own employees? Rewrite this program so that it works properly.

DECLARE
   TYPE execute_action_t IS REF CURSOR RETURN employee%ROWTYPE;
   ceo_cv execute_action_t;
   v_minbonus REAL := 1000000;
BEGIN
   OPEN ceo_cv FOR
      'SELECT CEO FROM compensation
        WHERE layoffs > 1000
          AND bonus > :minbonus';
   FOR rec IN ceo_cv
   LOOP
      DBMS_OUTPUT.PUT_LINE (rec.ceo);
   END LOOP;
END;

13-25.

Write a function that executes a group function for any table, any column and a specified WHERE clause. Here is the header:

FUNCTION grpval (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   grpfunc IN VARCHAR2,
   whr IN VARCHAR2)
RETURN VARCHAR2;

And here is a example of applying this function:

SQL> EXEC p.l(grpval ('employee', 'salary', 'sum'))
60700