Search This Blog

Wednesday, August 31, 2011

Pl/sql Material Free download

Pl/SQL

Forward Declarations

PL/SQL is rather fussy about its requirement that you declare a variable, cursor, or module before you use it in your code. Otherwise, how can PL/SQL be sure that the way you are using the object is appropriate? Since modules can call other modules, however, you can encounter situations where it is completely impossible to define all modules before any references to those modules are made. What if program A calls program B and program B calls program A? PL/SQL supports mutual recursion, where two or more programs directly or indirectly call each other.
If you do find yourself committed to mutual recursion, you will be very glad to hear that PL/SQL supports the forward declaration of local modules, which declares a module in advance of the actual definition of that program. This declaration makes that program available to be called by other programs even before the program definition.
Remember that both procedures and functions have a header and a body. A forward declaration consists simply of the program header, followed by a semicolon (;). This construction is called the module header. This header, which must include the parameter list (and RETURN clause if a function) is all the information PL/SQL needs about a module in order to declare it and resolve any references to that module; a module should, after all, be a little black box.
The following example will illustrate the technique. I define two mutually recursive functions within a procedure. Consequently I have to declare just the header of my second function, total_cost, before the full declaration of net_profit:
PROCEDURE perform_calcs (year_in IN INTEGER)
IS
   /* Header only for total_cost function. */
   FUNCTION total_cost (. . .)  RETURN NUMBER;
 
   /* The net_profit function uses total_cost. */
   FUNCTION net_profit (. . .) RETURN NUMBER
   IS
   BEGIN
      RETURN tot_sales (. . .) - total_cost (. . .);
   END;
 
   /* The total_cost function uses net_profit. */
   FUNCTION total_cost (. . .)  RETURN NUMBER
   IS
   BEGIN
      IF net_profit (. . .) < 0
      THEN
         RETURN 0;
      ELSE
         RETURN . . .;
      END IF;
   END;
BEGIN
   . . .
END;
Here are some rules to remember concerning forward declarations:
·         You cannot make forward declarations of a variable or cursor. This technique works only with modules (procedures and functions).
·         The definition for a forwardly-declared program must be contained in the declaration section of the same PL/SQL block (anonymous block, procedure, function, or package) in which you code the forward declaration.
In some situations, you absolutely require forward declarations; in most situations, they just help make your code more readable and presentable. As with every other advanced or unusual feature of the PL/SQL language, use forward declarations only when you really need the functionality. Otherwise, the declarations simply add to the clutter of your program, which is the last thing you want.

The EXECUTE IMMEDIATE Statement

Use EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement:
EXECUTE IMMEDIATE SQL_string
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];
SQL_string
A string expression containing the SQL statement or PL/SQL block define_variable
A variable that receives a column value returned by a query record
A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query
bind_argument
An expression whose value is passed to the SQL statement or PL/SQL block
INTO clause
Use for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of compatible type.
USING clause
Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is only relevant for PL/SQL, however; the default is IN, which is the only kind of bind argument you would have for SQL statements.
You can use EXECUTE IMMEDIATE for any SQL statement or PL/SQL block, except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language -- SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.
When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL data type. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL.
NDS supports all SQL datatypes available in Oracle8i. So, for example, define variables and bind arguments can be collections, large objects (LOBs), instances of an object type, and REFs. On the other hand, NDS does not support datatypes that are specific to PL/SQL, such as Booleans, index-by tables, and user-defined record types. The INTO clause may, however, contain a PL/SQL record.
Let's take a look at a few examples:
1.      Create an index:
EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employee (last_name)';
It can't get much easier than that, can it?
2.      Create a stored procedure that will execute any DDL statement:
CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;
/
With execDDL in place, I can create that same index as follows:
execDDL ('CREATE INDEX emp_u_1 ON employee (last_name)');
3.      Obtain the count of rows in any table, in any schema, for the specified WHERE clause:
/* Filename on companion disk: tabcount.sf */
CREATE OR REPLACE FUNCTION tabCount (
   tab IN VARCHAR2,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
   retval INTEGER;
BEGIN
   EXECUTE IMMEDIATE
      'SELECT COUNT(*) 
         FROM ' || NVL (sch, USER) || '.' || tab ||
      ' WHERE ' || NVL (whr, '1=1')
      INTO retval;
   RETURN retval;
END;
/
So now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program, as in the following:
BEGIN
   IF tabCount ('emp', 'deptno = ' || v_dept) > 100
   THEN
      DBMS_OUTPUT.PUT_LINE ('Growing fast!');
   END IF;
4.      Here's a function that lets you update the value of any numeric column in any table. It's a function because it returns the number of rows that have been updated.
/* Filename on companion disk: updnval.sf */
CREATE OR REPLACE FUNCTION updNVal (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   val IN NUMBER,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
BEGIN
   EXECUTE IMMEDIATE
      'UPDATE ' || NVL (sch, USER) || '.' || tab ||
      '   SET ' || col || ' = :the_value 
        WHERE ' || NVL (whr, '1=1')
     USING val;
   RETURN SQL%ROWCOUNT;
END;
/
Where I come from, that is a very small amount of code to achieve all of that flexibility! This example introduces the bind argument: after the UPDATE statement is parsed, the PL/SQL engine replaces the :the_value placeholder with the value in the val variable. Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute that I have already been using for static DML statements.
5.      Suppose that I need to run a different stored procedure at 9 a.m. each day of the week. Each program's name has this structure: DAYNAME_set_schedule. Each procedure has the same four arguments: you pass in employee_id and hour for the first meeting of the day; it returns the name of the employee and the number of appointments for the day. I can use dynamic PL/SQL to handle this situation:
6.  /* Filename on companion disk: run9am.sp */
7.  CREATE OR REPLACE PROCEDURE run_9am_procedure (
8.     id_in IN employee.employee_id%TYPE,
9.     hour_in IN INTEGER)
10.IS
11.   v_apptCount INTEGER;
12.   v_name VARCHAR2(100);
13.BEGIN
14.   EXECUTE IMMEDIATE
15.      'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') || 
16.         '_set_schedule (:id, :hour, :name, :appts); END;'
17.     USING IN 
18.        id_in, IN hour_in, OUT v_name, OUT v_apptCount;
19. 
20.   DBMS_OUTPUT.PUT_LINE (
21.      'Employee ' || v_name || ' has ' || v_apptCount ||
22.      ' appointments on ' || TO_CHAR (SYSDATE));
23.END;
/

2.1 The EXECUTE IMMEDIATE Statement

Use EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement:
EXECUTE IMMEDIATE SQL_string
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];
SQL_string
A string expression containing the SQL statement or PL/SQL block
define_variable
A variable that receives a column value returned by a query
record
A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query
bind_argument
An expression whose value is passed to the SQL statement or PL/SQL block
INTO clause
Use for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of compatible type.
USING clause
Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is only relevant for PL/SQL, however; the default is IN, which is the only kind of bind argument you would have for SQL statements.
You can use EXECUTE IMMEDIATE for any SQL statement or PL/SQL block, except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language -- SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.
When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL datatype. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL.
NDS supports all SQL datatypes available in Oracle8i. So, for example, define variables and bind arguments can be collections, large objects (LOBs), instances of an object type, and REFs. On the other hand, NDS does not support datatypes that are specific to PL/SQL, such as Booleans, index-by tables, and user-defined record types. The INTO clause may, however, contain a PL/SQL record.
Let's take a look at a few examples:
1.      Create an index:
EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employee (last_name)';
It can't get much easier than that, can it?
2.      Create a stored procedure that will execute any DDL statement:
CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;
/
With execDDL in place, I can create that same index as follows:
execDDL ('CREATE INDEX emp_u_1 ON employee (last_name)');
3.      Obtain the count of rows in any table, in any schema, for the specified WHERE clause:
/* Filename on companion disk: tabcount.sf */
CREATE OR REPLACE FUNCTION tabCount (
   tab IN VARCHAR2,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
   retval INTEGER;
BEGIN
   EXECUTE IMMEDIATE
      'SELECT COUNT(*) 
         FROM ' || NVL (sch, USER) || '.' || tab ||
      ' WHERE ' || NVL (whr, '1=1')
      INTO retval;
   RETURN retval;
END;
/
So now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program, as in the following:
BEGIN
   IF tabCount ('emp', 'deptno = ' || v_dept) > 100
   THEN
      DBMS_OUTPUT.PUT_LINE ('Growing fast!');
   END IF;
4.      Here's a function that lets you update the value of any numeric column in any table. It's a function because it returns the number of rows that have been updated.
/* Filename on companion disk: updnval.sf */
CREATE OR REPLACE FUNCTION updNVal (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   val IN NUMBER,
   whr IN VARCHAR2 := NULL,
   sch IN VARCHAR2 := NULL)
   RETURN INTEGER
IS
BEGIN
   EXECUTE IMMEDIATE
      'UPDATE ' || NVL (sch, USER) || '.' || tab ||
      '   SET ' || col || ' = :the_value 
        WHERE ' || NVL (whr, '1=1')
     USING val;
   RETURN SQL%ROWCOUNT;
END;
/
Where I come from, that is a very small amount of code to achieve all of that flexibility! This example introduces the bind argument: after the UPDATE statement is parsed, the PL/SQL engine replaces the :the_value placeholder with the value in the val variable. Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute that I have already been using for static DML statements.
5.      Suppose that I need to run a different stored procedure at 9 a.m. each day of the week. Each program's name has this structure: DAYNAME_set_schedule. Each procedure has the same four arguments: you pass in employee_id and hour for the first meeting of the day; it returns the name of the employee and the number of appointments for the day. I can use dynamic PL/SQL to handle this situation:
/* Filename on companion disk: run9am.sp */
CREATE OR REPLACE PROCEDURE run_9am_procedure (
   id_in IN employee.employee_id%TYPE,
   hour_in IN INTEGER)
IS
   v_apptCount INTEGER;
   v_name VARCHAR2(100);
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') || 
         '_set_schedule (:id, :hour, :name, :appts); END;'
     USING IN 
        id_in, IN hour_in, OUT v_name, OUT v_apptCount;
 
   DBMS_OUTPUT.PUT_LINE (
      'Employee ' || v_name || ' has ' || v_apptCount ||
      ' appointments on ' || TO_CHAR (SYSDATE));
END;
/

Autonomous Transactions

One long-standing request from PL/SQL developers has been to have the ability to execute and then save or cancel certain Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE) without affecting the overall session's transaction. You can now do this with autonomous transactions.
Where would you find autonomous transactions useful in your applications? Here are some ideas:
Logging mechanism
This is the classic example of the need for an autonomous transaction. You need to log error information in a database table, but don't want that log entry to be a part of the logical transaction.
Commits or rollbacks in your database triggers
Finally! If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code.
Retry counter
Autonomous transactions can help you keep track of how many times a user tries to connect to a database or get access to a resource (you'll reject access after a certain number of attempts).
Software usage meter
A similar type of situation is when you want to track how often a program is called during an application session. In fact, autonomous transactions are helpful in meeting any application requirement that calls for persistently storing a state (how many times did Joe try to update the salary column?).
Reusable application components
You are building an Internet application. You want to combine components from many different vendors and layers. They need to interact in certain well-defined ways. If when one component commits, it affects all other aspects of your application, it will not function well in this environment.
When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the rest of your session. That block becomes an independent transaction that is started by another transaction, referred to as the main transaction. Within the autonomous transaction block, the main transaction is suspended. You perform your SQL operations, commit or roll back those operations, and then resume the main transaction.
There isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section:
PRAGMA AUTONOMOUS_TRANSACTION;
Here is a very simple logging mechanism relying on the autonomous transaction feature to save changes to the log without affecting the rest of the session's transaction:
PROCEDURE write_log (
   code IN INTEGER, text IN VARCHAR2)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO log VALUES (
      code, text, 
      USER, SYSDATE
      );
   COMMIT:
END;


Bulk Querying with the BULK COLLECT Clause

So now you know how to perform bulk DML with FORALL. What about bulk querying? I showed an example earlier of the kind of code that cries out for a bulk transfer. Here is the executable section of that example:
BEGIN
   FOR bad_car IN major_polluters
   LOOP
      names.EXTEND;
      names (major_polluters%ROWCOUNT) := bad_car.name;
      mileages.EXTEND;
      mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
   END LOOP;
 
   ... now work with data in the arrays ...
END;
Not only do I have to write a bunch of code, but it is also much slower than I would like, because of the context switches.
To help out in this scenario, PL/SQL now offers the BULK COLLECT keywords. This clause in your cursor (explicit or implicit) tells the SQL engine to bulk bind the output from the multiple rows fetched by the query into the specified collections before returning control to the PL/SQL engine. The syntax for this clause is:
... BULK COLLECT INTO collection_name[, collection_name] ...
where collection_name identifies a collection.
Here are some rules and restrictions to keep in mind when using BULK COLLECT:
·         You can use these keywords in any of the following clauses: SELECT INTO, FETCH INTO, and RETURNING INTO.
·         The collections you reference can only store scalar values (string, number, date). In other words, you cannot fetch a row of data into a record structure that is a row in a collection.
·         The SQL engine automatically initializes and extends the collections you reference in the BULK COLLECT clause. It starts filling the collections at index 1, inserts elements consecutively (densely), and overwrites the values of any elements that were previously defined.
·         You cannot use the SELECT...BULK COLLECT statement in a FORALL statement.
Let's explore these rules and the usefulness of BULK COLLECT through a series of examples.
First, here is a recoding of the "major polluters" example using BULK COLLECT:
DECLARE
   names name_varray;
   mileages number_varray;
BEGIN
   SELECT name, mileage
     FROM cars_and_trucks
     BULK COLLECT INTO names, mileages
    WHERE vehicle_type IN ('SUV', 'PICKUP');
 
   ... now work with data in the arrays ...
END;
I am now able to remove the initialization and extension code from the row-by-row fetch implementation.
But I don't have to rely on implicit cursors to get this job done. Here is another re-working of the major polluters example, retaining the explicit cursor:
DECLARE
   CURSOR major_polluters IS
      SELECT name, mileage
        FROM cars_and_trucks
       WHERE vehicle_type IN ('SUV', 'PICKUP');
   names name_varray;
   mileages number_varray;
BEGIN
   OPEN major_polluters;
   FETCH major_polluters BULK COLLECT INTO names, mileages;
 
   ... now work with data in the arrays ...
END;
recommend that you use this second, explicit cursor-based approach -- and that you store your cursors in packages, so that they can be reused. In fact, the optimal approach would involve a bundling of these operations into a procedure, as follows:
/* Filename on companion disk: polluters.pkg */
CREATE OR REPLACE PACKAGE pollution
IS
   CURSOR major_polluters (typelist IN VARCHAR2)
   IS
      SELECT name, mileage
        FROM cars_and_trucks
       WHERE INSTR (typelist, vehicle_type) > 0;
 
   PROCEDURE get_major_polluters (
      typelist IN VARCHAR2,
      names OUT name_varray,
      mileages OUT number_varray);
END;
/
 
CREATE OR REPLACE PACKAGE BODY pollution
IS
   PROCEDURE get_major_polluters (
      typelist IN VARCHAR2,
      names OUT name_varray,
      mileages OUT number_varray)
   IS
   BEGIN
      IF major_polluters%ISOPEN
      THEN
         CLOSE major_polluters;
      END IF;
      OPEN major_polluters (typelist);
      FETCH major_polluters BULK COLLECT INTO names, mileages;
      CLOSE major_polluters;
   END;
END;
/
Then I can populate my arrays with a minimum of fuss and a maximum of reusability (of both code and SQL):
DECLARE
   names name_varray;
   mileages number_varray;
BEGIN
   pollution.get_major_polluters ('SUV,PICKUP');
 
   ... now work with data in the arrays ...
END;

5.3.1 Restricting Bulk Collection with ROWNUM

There is no regulator mechanism built into BULK COLLECT. If your SQL statement identifies 100,000 rows of data, then the column values of all 100,000 rows will be loaded into the target collections. This can, of course, cause serious problems in your application -- and in system memory. Remember: these collections are allocated for each session. So if you have 100 users all running the same program that bulk collects 100,000 rows of information, then real memory is needed for a total of 10 million rows.
What can you do about this potentially hazardous scenario? First of all (as should be common sense in your application regardless of the use of BULK COLLECT), be careful about the queries you write and those you offer to developers and/or users to run. You shouldn't provide unrestricted access to very large tables.
You can also fall back on ROWNUM to limit the number of rows processed by your query. For example, suppose that my cars_and_trucks table has a very large number of rows of vehicles that qualify as major polluters. I could then add a ROWNUM condition to my WHERE clause and another parameter to my packaged cursor as follows:
CREATE OR REPLACE PACKAGE pollution
IS
   CURSOR major_polluters (
      typelist IN VARCHAR2, maxrows IN INTEGER := NULL)
   IS
      SELECT name, mileage
        FROM cars_and_trucks
       WHERE INSTR (typelist, vehicle_type) > 0
         AND ROWNUM < LEAST (maxrows, 10000);
 
   PROCEDURE get_major_polluters (
      typelist IN VARCHAR2,
      names OUT name_varray,
      mileages OUT number_varray);
END;
/
Now there is no way that anyone can ever get more than 10,000 rows in a single query -- and the user of that cursor (an individual developer) can also add a further regulatory capability by overriding that 10,000 with an even smaller number.

5.3.2 Bulk Fetching of Multiple Columns

As you have seen in previous examples, you certainly can bulk fetch the contents of multiple columns. However, you must fetch them into separate collections, one per column.
You cannot fetch into a collection of records (or objects). The following example demonstrates the error that you will receive if you try to do this:
DECLARE
   TYPE VehTab IS TABLE OF cars_and_trucks%ROWTYPE;
   gas_guzzlers VehTab;
   CURSOR low_mileage_cur IS SELECT * FROM cars_and_trucks WHERE mileage < 10;
BEGIN
   OPEN low_mileage_cur;
   FETCH low_mileage_cur BULK COLLECT INTO gas_guzzlers;
END;
/
When I run this code, I get the following somewhat obscure error message:
PLS-00493: invalid reference to a server-side object or 
           function in a local context
You will instead have to write this block as follows:
DECLARE
   guzzler_type name_varray;
   guzzler_name name_varray;
   guzzler_mileage number_varray;
 
   CURSOR low_mileage_cur IS 
      SELECT vehicle_type, name, mileage
        FROM cars_and_trucks WHERE mileage < 10;
BEGIN
   OPEN low_mileage_cur;
   FETCH low_mileage_cur BULK COLLECT 
    INTO guzzler_type, guzzler_name, guzzler_mileage;
END;
/

5.3.3 Using the RETURNING Clause with Bulk Operations

You've now seen BULK COLLECT put to use for both implicit and explicit query cursors. You can also use BULK COLLECT inside a FORALL statement, in order to take advantage of the RETURNING clause.
The RETURNING clause, new to Oracle8, allows you to obtain information (such as a newly updated value for a salary) from a DML statement. RETURNING can help you avoid additional queries to the database to determine the results of DML operations that just completed.
Suppose Congress has passed a law (overriding the almost certain presidential veto) requiring that a company pay its highest-compensated employee no more than 50 times the salary of its lowest-paid employee.[1] I work in the IT department of the newly merged company Northrop-Ford-Mattel-Yahoo-ATT, which employs a total of 250,000 workers. The word has come down from on high: the CEO is not taking a pay cut, so we need to increase the salaries of everyone who makes less than 50 times his 2004 total compensation package of $145 million -- and decrease the salaries of all upper management except for the CEO. After all, somebody's got to make up for this loss in profit.
[1] Currently in the United States, the average is more like 250 times, a very inequitable situation that almost certainly causes hundreds of thousands of children to go hungry each day in our very rich nation.
Wow! I have lots of updating to do, and I want to use FORALL to get the job done as quickly as possible. However, I also need to perform various kinds of processing on the employee data and then print a report showing the change in salary for each affected employee. That RETURNING clause would come in awfully handy here, so let's give it a try.
See the onlyfair.sql file on the companion disk for all of the steps shown here, plus table creation and INSERT statements.
First, I'll create a reusable function to return the compensation for an executive:
/* Filename on companion disk: onlyfair.sql */
FUNCTION salforexec (title_in IN VARCHAR2) RETURN NUMBER
IS
   CURSOR ceo_compensation IS
      SELECT salary + bonus + stock_options + 
             mercedes_benz_allowance + yacht_allowance
        FROM compensation
       WHERE title = title_in;       
   big_bucks NUMBER;
BEGIN
   OPEN ceo_compensation; 
   FETCH ceo_compensation INTO big_bucks;
   RETURN big_bucks;
END;
/
In the main block of the update program, I declare a number of local variables and the following query to identify underpaid employees and overpaid employees who are not lucky enough to be the CEO:
DECLARE  
   big_bucks NUMBER := salforexec ('CEO');
   min_sal NUMBER := big_bucks / 50;
   names name_varray;
   old_salaries number_varray;
   new_salaries number_varray;
   
   CURSOR affected_employees (ceosal IN NUMBER)
   IS
      SELECT name, salary + bonus old_salary
        FROM compensation
       WHERE title != 'CEO'
         AND ((salary + bonus < ceosal / 50) 
              OR (salary + bonus > ceosal / 10)) ;
At the start of my executable section, I load all this data into my collections with a BULK COLLECT query:
OPEN affected_employees (big_bucks);
FETCH affected_employees 
   BULK COLLECT INTO names, old_salaries;
Then I can use the names collection in my FORALL update:
FORALL indx IN names.FIRST .. names.LAST
   UPDATE compensation
      SET salary = 
          DECODE (
             GREATEST (min_sal, salary), 
                min_sal, min_sal,
             salary / 5)
    WHERE name = names (indx)
    RETURNING salary BULK COLLECT INTO new_salaries;
I use DECODE to give an employee either a major boost in yearly income or an 80% cut in pay to keep the CEO comfy. I end it with a RETURNING clause that relies on BULK COLLECT to populate a third collection: the new salaries.
Finally, since I used RETURNING and don't have to write another query against the compensation table to obtain the new salaries, I can immediately move to report generation:
FOR indx IN names.FIRST .. names.LAST
LOOP
   DBMS_OUTPUT.PUT_LINE (
      RPAD (names(indx), 20) || 
      RPAD (' Old: ' || old_salaries(indx), 15) ||
      ' New: ' || new_salaries(indx)
      );
END LOOP;
Here, then, is the report generated from the onlyfair.sql script:
John DayAndNight     Old: 10500     New: 2900000
Holly Cubicle        Old: 52000     New: 2900000
Sandra Watchthebucks Old: 22000000  New: 4000000
Now everyone can afford quality housing and health care; tax revenue at all levels will increase (nobody's a better tax deadbeat than the ultra-rich), so public schools can get the funding they need. Hey, and rich people are even still rich -- just not as rich as before. Now that is what I call a humming economy!
TIP: The RETURNING column values or expressions returned by each execution in FORALL are added to the collection after the values returned previously. If you use RETURNING inside a non-bulk FOR loop, previous values are overwritten by the latest DML execution.

Cursor Variables

In PL/SQL Release 2.3, available with the release of Oracle Server Release 7.3, you can create and use cursor variables. Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is instead a (Release 2.3) reference to that work area. Explicit and implicit cursors are both static in that they are tied to specific queries. The cursor variable can be opened for any query, even different queries within a single program execution.
The most important benefit of the cursor variable is that it provides a mechanism for passing results of queries (the rows returned by fetches against a cursor) between different PL/SQL programs -- even between client and server PL/SQL programs. Prior to PL/SQL Release 2.3, you would have had to fetch all data from the cursor, store it in PL/SQL variables (perhaps a PL/SQL table), and then pass those variables as arguments. With cursor variables, you simply pass the reference to that cursor. This improves performance and streamlines your code.
It also means that the cursor is, in effect, shared among the programs which have access to the cursor variable. In a client-server environment, for example, a program on the client side could open and start fetching from the cursor variable, then pass that variable as an argument to a stored procedure on the server. This stored program could then continue fetching and pass control back to the client program to close the cursor. You can also perform the same steps between different stored programs, on the same or different database instances.

Cursor Variables in PL/SQL Release 2.2

Cursor variables first became available in PL/SQL Release 2.2. This first version of cursor variables allowed you to open and close the cursor objects within PL/SQL, but you could fetch through these cursor variables only within a host language (using the Oracle Call Interface -- OCI -- or a precompiler like Pro*C). It was not until Release 2.3 that the PL/SQL language made cursor variables available for "self-contained" execution, independent of any host language.
Because the focus of this book is on standalone PL/SQL development, I present cursor variables as a PL/SQL Release 2.3 enhancement. If you do have PL/SQL Release 2.2 and work with PL/SQL in a host language environment, you can still use cursor variables. Just don't try to FETCH within PL/SQL and don't expect any of the cursor attributes to be available for your cursor variables.
NOTE: The client-server aspect of this sharing will only really come into play when the Oracle Developer/2000 tools are converted to use PL/SQL Release 2.3 or above.
This process, shown in Figure 6.2, offers dramatic new possibilities for data sharing and cursor management in PL/SQL programs.

Figure 6.2: Referencing a cursor variable across two programs

Figure 6.2
The code you write to take advantage of cursor variables is very similar to that for explicit cursors. The following example declares a cursor type (called a REF CURSOR type) for the company table, then opens, fetches from, and closes the cursor:
DECLARE
   /* Create the cursor type. */
   TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
 
   /* Declare a cursor variable of that type. */
   company_curvar company_curtype;
 
   /* Declare a record with same structure as cursor variable. */
   company_rec company%ROWTYPE;
BEGIN
   /* Open the cursor variable, associating with it a SQL statement. */
   OPEN company_curvar FOR SELECT * FROM company;
 
   /* Fetch from the cursor variable. */
   FETCH company_curvar INTO company_rec;
 
   /* Close the cursor object associated with variable. */
   CLOSE company_curvar;
END;
That looks an awful lot like explicit cursor operations, except for the following:
·         The REF CURSOR type declaration
·         The OPEN FOR syntax which specified the query at the time of the open
While the syntax is very similar, the fact that the cursor variable is a variable opens up many new opportunities in your programs. These are explored in the remainder of this section.

6.12.1 Features of Cursor Variables

Cursor variables let you:
·         Associate a cursor variable with different queries at different times in your program execution. In other words, a single cursor variable can be used to fetch from different result sets.
·         Pass a cursor variable as an argument to a procedure or function. You can, in essence, share the results of a cursor by passing the reference to that result set.
·         Employ the full functionality of static PL/SQL cursors for cursor variables. You can OPEN, CLOSE, and FETCH with cursor variables within your PL/SQL programs. You can reference the standard cursor attributes -- %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT -- for cursor variables.
·         Assign the contents of one cursor (and its result set) to another cursor variable. Because the cursor variable is a variable, it can be used in assignment operations. There are, however, restrictions on referencing this kind of variable, addressed later in this chapter.

6.12.2 Similarities to Static Cursors

One of the key design requirements for cursor variables was that as much as possible the semantics used to manage cursor objects would be the same as that of static cursors. While the declaration of a cursor variable and the syntax for opening it are enhanced, the following cursor operations are unchanged for cursor variables:
·         The CLOSE statement. In the following example I declare a REF CURSOR type and a cursor variable based on that type. Then I close the cursor variable using the same syntax as for that of a static cursor:
·         DECLARE
·            TYPE var_cur_type IS REF CURSOR;
·            var_cur var_cur_type;
·         BEGIN
·            CLOSE var_cur;
END;
·         Cursor attributes. You can use any of the four cursor attributes with exactly the same syntax as for that of a static cursor. The rules governing the use and values returned by those attributes match that of explicit cursors. If I have declared a variable cursor as in the previous example, I could use all the cursor attributes as follows:
·         var_cur%ISOOPEN
·         var_cur%FOUND
·         var_cur%NOTFOUND
var_cur%ROWCOUNT
·         Fetching from the cursor variable. You use the same FETCH syntax when fetching from a cursor variable into local PL/SQL data structures. There are, however, additional rules applied by PL/SQL to make sure that the data structures of the cursor variable's row (the set of values returned by the cursor object) match that of the data structures to the right of the INTO keyword. These rules are discussed in Section 6.12.6, "Rules for Cursor Variables".
Because the syntax for these aspects of cursor variables remain unchanged, I won't cover them again in the remainder of this section. Instead I will focus on the new capabilities available and the changed syntax required for cursor variables.

6.12.3 Declaring REF CURSOR Types and Cursor Variables

Just as with a PL/SQL table or a programmer-defined record, you must perform two distinct declaration steps in order to create a cursor variable:
1.      Create a referenced cursor TYPE.
2.      Declare the actual cursor variable based on that type.
The syntax for creating a referenced cursor type is as follows:
TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];
where cursor_type_name is the name of the type of cursor and return_type is the RETURN data specification for the cursor type. The return_type can be any of the data structures valid for a normal cursor RETURN clause, defined using the %ROWTYPE attribute or by referencing a previously-defined record TYPE.
Notice that the RETURN clause is optional with the REF CURSOR type statement. Both of the following declarations are valid:
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
TYPE generic_curtype IS REF CURSOR;
The first form of the REF CURSOR statement is called a strong type because it attaches a record type (or row type) to the cursor variable type at the moment of declaration. Any cursor variable declared using that type can only be used with SQL statement and FETCH INTO data structures which match the specified record type. The advantage of a strong REF TYPE is that the compiler can determine whether or not the developer has properly matched up the cursor variable's FETCH statements with its cursor object's query list.
The second form of the REF CURSOR statement, in which the RETURN clause is missing, is called a weak type. This cursor variable type is not associated with any record data structure. Cursor variables declared without the RETURN clause can be used in much more flexible ways than the strong type. They can be used with any query, with any rowtype structure -- varying even within the course of a single program.

6.12.3.1 Declaring cursor variables

The syntax for declaring a cursor variable is:
cursor_name cursor_type_name;
where cursor_name is the name of the cursor and cursor_type_name is the name of the type of cursor previously defined with a TYPE statement.
Here is an example of the creation of a cursor variable:
DECLARE
   /* Create a cursor type for sports cars. */
   TYPE sports_car_cur_type IS REF CURSOR RETURN car%ROWTYPE;
 
   /* Create a cursor variable for sports cars. */
   sports_car_cur sports_car_cur_type;
BEGIN
   ...
END;
It is very important to distinguish between declaring a cursor variable and creating an actual cursor object -- the result set identified by the cursor SQL statement. The cursor variable is nothing more than a reference or pointer. A constant is nothing more than a value, whereas a variable points to its value. Similarly, a static cursor acts as a constant, whereas a cursor variable points to a cursor object. These distinctions are shown in Figure 6.3. Notice that two different cursor variables in different programs both refer to the same cursor object.

Figure 6.3: The referencing character of cursor variables

Figure 6.3
Declaration of a cursor variable does not create a cursor object. To do that, you must instead use the OPEN FOR syntax to create a new cursor object and assign it to the variable.

6.12.4 Opening Cursor Variables

You assign a value (the cursor object) to a cursor when you OPEN the cursor. So the syntax for the OPEN statement is now modified in PL/SQL Release 2.3 to accept a SELECT statement after the FOR clause, as shown below:
OPEN cursor_name FOR select_statement;
where cursor_name is the name of a cursor or cursor variable and select_statement is a SQL SELECT statement.
For strong REF CURSOR type cursor variables, the structure of the SELECT statement (the number and datatypes of the columns) must match or be compatible with the structure specified in the RETURN clause of the type statement. Figure 6.4 offers an example of the kind of compatibility required. Figure 6.4" contains the full set of compatibility rules.

Figure 6.4: Compatible REF CURSOR rowtype and SELECT list

Figure 6.4
If cursor_name is a cursor variable defined with a weak REF CURSOR type, you can OPEN it for any query, with any structure. In the following example, I open (assign a value to) the cursor variable twice, with two different queries:
DECLARE
   TYPE emp_curtype IS REF CURSOR;
   emp_curvar emp_curtype;
BEGIN
   OPEN emp_curvar FOR SELECT * FROM emp;
   OPEN emp_curvar FOR SELECT employee_id FROM emp;
   OPEN emp_curvar FOR SELECT company_id, name FROM company;
END;
That last open didn't even have anything to do with the employee table!
If the cursor variable has not yet been assigned to any cursor object, the OPEN FOR statement implicitly creates an object for the variable.
If at the time of the OPEN the cursor variable already is pointing to a cursor object, then OPEN FOR does not create a new object. Instead, it reuses the existing object and attaches a new query to that object. The cursor object is maintained separately from the cursor or query itself.

6.12.5 Fetching from Cursor Variables

As mentioned earlier, the syntax for a FETCH statement using a cursor variable is the same as that for static cursors:
FETCH <cursor variable name> INTO <record name>;
FETCH <cursor variable name> INTO <variable name>, <variable name> ...;
When the cursor variable was declared with a strong REF CURSOR type, the PL/SQL compiler makes sure that the data structure(s) listed after the INTO keyword are compatible with the structure of the query associated with cursor variable.

6.12.5.1 Strong and weak REF CURSOR types

If the cursor variable is of the weak REF CURSOR type, the PL/SQL compiler cannot perform the same kind of check. Such a cursor variable can FETCH into any data structures, because the REF CURSOR type it is not identified with a rowtype at the time of declaration. At compile time, there is no way to know which cursor object (and associated SQL statement) will be assigned to that variable.
Consequently, the check for compatibility must happen at run time, when the FETCH is about to be executed. At this point, if the query and the INTO clause do not structurally match (and PL/SQL will use implicit conversions if necessary and possible), then the PL/SQL runtime engine will raise the predefined ROWTYPE_MISMATCH exception.

6.12.5.2 Handling the ROWTYPE_MISMATCH exception

Before PL/SQL actually performs its FETCH, it checks for compatibility. As a result, you can trap the ROWTYPE_MISMATCH exception and attempt to FETCH from the cursor variable using a different INTO clause -- and you will not have skipped any rows in the result set.
Even though you are executing a second FETCH statement in your program, you will still retrieve the first row in the result set of the cursor object's query. This functionality comes in especially handy for weak REF CURSOR types.
In the following example, a centralized real estate database stores information about properties in a variety of tables, one for homes, another for commercial properties, etc. There is also a single, central table which stores an address and a building type (home, commercial, etc.). I use a single procedure to open a weak REF CURSOR variable for the appropriate table, based on the street address. Each individual real estate office can then call that procedure to scan through the matching properties:
1.      Define my weak REF CURSOR type:
TYPE building_curtype IS REF CURSOR;
2.      Create the procedure. Notice that the mode of the cursor variable parameter is IN OUT:
PROCEDURE open_site_list
   (address_in IN VARCHAR2,
    site_cur_inout IN OUT building_curtype)
IS
   home_type CONSTANT INTEGER := 1;
   commercial_type CONSTANT INTEGER := 2;
 
   /* A static cursor to get building type. */
   CURSOR site_type_cur IS
      SELECT site_type FROM property_master
       WHERE address = address_in;
   site_type_rec site_type_cur%ROWTYPE;
 
BEGIN
   /* Get the building type for this address. */
   OPEN site_type_cur;
   FETCH site_type_cur INTO site_type_rec;
   CLOSE site_type_cur;
 
   /* Now use the site type to select from the right table.*/
   IF site_type_rec.site_type =  home_type
   THEN
      /* Use the home properties table. */
      OPEN site_cur_inout FOR
         SELECT * FROM home_properties
          WHERE address LIKE '%' || address_in || '%';
 
   ELSIF site_type_rec.site_type =  commercial_type
   THEN
      /* Use the commercial properties table. */
      OPEN site_cur_inout FOR
         SELECT * FROM commercial_properties
          WHERE address LIKE '%' || address_in || '%';
   END IF;
END open_site_list;
3.      Now that I have my open procedure, I can use it to scan properties.
In the following example, I pass in the address and then try to fetch from the cursor, assuming a home property. If the address actually identifies a commercial property, PL/SQL will raise the ROWTYPE_MISMATCH exception (incompatible record structures). The exception section then fetches again, this time into a commercial building record, and the scan is complete.[2]
[2] The "prompt" and "show" programs referenced in the example interact with users and are not documented here.
DECLARE
   /* Declare a cursor variable. */
   building_curvar building_curtype;
 
   /* Define record structures for two different tables. */
   home_rec home_properties%ROWTYPE;
   commercial_rec commercial_properties%ROWTYPE;
BEGIN
   /* Get the address from the user. */
   prompt_for_address (address_string);
 
   /* Assign a query to the cursor variable based on the address. */
   open_site_list (address_string, building_curvar);
 
   /* Give it a try! Fetch a row into the home record. */
   FETCH building_curvar INTO home_rec;
 
   /* If I got here, the site was a home, so display it. */
   show_home_site (home_rec);
EXCEPTION
   /* If the first record was not a home... */
   WHEN ROWTYPE_MISMATCH
   THEN
      /* Fetch that same 1st row into the commercial record. */
      FETCH building_curvar INTO commercial_rec;
 
      /* Show the commercial site info. */
      show_commercial_site (commercial_rec);
END;
 
 

6.12.6 Rules for Cursor Variables

This section examines in more detail the rules and issues regarding the use of cursor variables in your programs. This includes rowtype matching rules, cursor variable aliases, and scoping issues.
Remember that the cursor variable is a reference to a cursor object or query in the database. It is not the object itself. A cursor variable is said to "refer to a given query" if either of the following is true:
·         An OPEN statement FOR that query was executed with the cursor variable.
·         A cursor variable was assigned a value from another cursor variable that refers to that query.
You can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. In order to perform such actions between cursor variables (and to bind a cursor variable to a parameter), the different cursor variables must follow a set of compile-time and runtime rowtype matching rules.

6.12.6.1 Compile-time rowtype matching rules

These are the rules that PL/SQL follows at compile-time:
·         Two cursor variables (including procedure parameters) are compatible for assignments and argument passing if any of the following are true:
o    Both variables (or parameters) are of a strong REF CURSOR type with the same <rowtype_name>.
o    Both variables (or parameters) are of some weak REF CURSOR type, regardless of the <rowtype_name>.
o    One variable (parameter) is of any strong REF CURSOR type, and the other is of any weak REF CURSOR type.
·         A cursor variable (parameter) of a strong REF CURSOR type may be OPEN FOR a query that returns a rowtype which is structurally equal to the <rowtype_name> in the original type declaration.
·         A cursor variable (parameter) of a weak REF CURSOR type may be OPEN FOR any query. The FETCH from such a variable is allowed INTO any list of variables or record structure.
In other words, if either of the cursor variables are of the weak REF CURSOR type, then the PL/SQL compiler cannot really validate whether the two different cursor variables will be compatible. That will happen at runtime; the rules are covered in the next section.

6.12.6.2 Run-time rowtype matching rules

These are the rules that PL/SQL follows at run time:
·         A cursor variable (parameter) of a weak REF CURSOR type may be made to refer to a query of any rowtype regardless of the query or cursor object to which it may have referred earlier.
·         A cursor variable (parameter) of a strong REF CURSOR type may be made to refer only to a query which matches structurally the <rowtype_name> of the RETURN clause of the REF CURSOR type declaration.
·         Two records (or lists of variables) are considered structurally matching with implicit conversions if both of the following are true:
o    The number of fields is the same in both records (lists).
o    For each field in one record (or variable on one list), a corresponding field in the second list (or variable in second list) has the same PL/SQL datatype, or one which can be converted implicitly by PL/SQL to match the first.
·         For a cursor variable (parameter) used in a FETCH statement, the query associated with the cursor variable must structurally match with implicit conversions the record or list of variables of the INTO clause of the FETCH statement. This is, by the way, the same rule used for static cursors.

6.12.6.3 Cursor variable aliases

If you assign one cursor variable to another cursor variable, those two cursor variables become aliases for the same cursor object. They share the reference to the cursor object (result set of the cursor's query). An action taken against the cursor object through one variable is also available to and reflected in the other variable.
The following anonymous block illustrates the way cursor aliases work:
  DECLARE
     TYPE curvar_type IS REF CURSOR;
     curvar1 curvar_type;
     curvar2 curvar_type;
     story fairy_tales%ROWTYPE;
  BEGIN
     /* Assign cursor object to curvar1. */
     OPEN curvar1 FOR SELECT * FROM fairy_tales;
 
    /* Assign same cursor object to curvar2. */
    curvar2 := curvar1;
 
    /* Fetch first record from curvar1. */
   FETCH curvar1 INTO story;
 
    /* Fetch second record from curvar2. */
    FETCH curvar2 INTO story;
 
    /* Close the cursor object by referencing curvar2. */
    CLOSE curvar2;
 
    /* This statement raises INVALID_CURSOR exception! */
    FETCH curvar1 INTO story;
END;
The following table is an explanation of cursor variable actions.
Lines
Action
1-5
Declare my weak REF CURSOR type and cursor variable through line 5.
8
Creates a cursor object and assigns it to curvar1.
11
Assigns that same cursor object to the second cursor variable, curvar2.
14
Fetches the first record using the curvar1 variable.
17
Fetches the second record using the curvar2 variable. (Notice that it doesn't matter which of the two variables you use. The pointer to the current record resides with the cursor object, not any particular variable.)
20
Closes the cursor object referencing curvar2.
23
Raises the INVALID_CURSOR exception when I try to fetch again from the cursor object. (When I closed the cursor through curvar2, it also closed it as far as curvar1 was concerned.)
Any change of state in a cursor object will be seen through any cursor variable which is an alias to that cursor object.

6.12.6.4 Scope of cursor object

The scope of a cursor variable is the same as that of a static cursor: the PL/SQL block in which the variable is declared (unless declared in a package, which makes the variable globally accessible). The scope of the cursor object to which a cursor variable is assigned, however, is a different matter.
Once an OPEN FOR creates a cursor object, that cursor object remains accessible as long as at least one active cursor variable refers to that cursor object. This means that you can create a cursor object in one scope (PL/SQL block) and assign it to a cursor variable. Then, by assigning that cursor variable to another cursor variable with a different scope, the cursor object remains accessible even if the original cursor variable has gone out of scope.
In the following example I use nested blocks to demonstrate how the cursor object can persist outside of the scope in which it was originally created:
DECLARE
   /* Define weak REF CURSOR type, cursor variable
      and local variable */
   TYPE curvar_type IS REF CURSOR;
   curvar1 curvar_type;
   do_you_get_it VARCHAR2(100);
BEGIN
   /*
   || Nested block which creates the cursor object and
   || assigns it to the curvar1 cursor variable.
   */
   DECLARE
      curvar2 curvar_type;
   BEGIN
      OPEN curvar2 FOR SELECT punch_line FROM jokes;
      curvar1 := curvar2;
   END;
   /*
   || The curvar2 cursor variable is no longer active,
   || but "the baton" has been passed to curvar1, which
   || does exist in the enclosing block. I can therefore
   || fetch from the cursor object, through this other
   || cursor variable.
   */
   FETCH curvar1 INTO do_you_get_it;
END;

6.12.7 Passing Cursor Variables as Arguments

You can pass a cursor variable as an argument in a call to a procedure or function. When you use a cursor variable in the parameter list of a program, you need to specify the mode of the parameter and the datatype (the REF CURSOR type).

6.12.7.1 Identifying the REF CURSOR type

In your program header, you must identify the REF CURSOR type of your cursor variable parameter. To do this, that cursor type must already be defined.
If you are creating a local module within another program (see Chapter 15 for more information about local modules), then you can also define the cursor type in the same program. It will then be available for the parameter. This approach is shown below:
DECLARE
   /* Define the REF CURSOR type. */
   TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;
 
   /* Reference it in the parameter list. */
   PROCEDURE open_query (curvar_out OUT curvar_type)
   IS
      local_cur curvar_type;
   BEGIN
      OPEN local_cur FOR SELECT * FROM company;
      curvar_out := local_cur;
   END;
BEGIN
   ...
END;
If you are creating a standalone procedure or function, then the only way you can reference a pre-existing REF CURSOR type is by placing that type statement in a package. All variables declared in the specification of a package act as globals within your session, so you can then reference this cursor type using the dot notation as shown below:
1.      Create the package with a REF CURSOR type declaration:
2.  PACKAGE company
3.  IS
4.     /* Define the REF CURSOR type. */
5.     TYPE curvar_type IS REF CURSOR RETURN company%ROWTYPE;
END package;
6.      In a standalone procedure, reference the REF CURSOR type by prefacing the name of the cursor type with the name of the package:
7.  PROCEDURE open_company (curvar_out OUT company.curvar_type) IS
8.  BEGIN
9.     ...
END;
See Chapter 16 for more information on this feature.

6.12.7.2 Setting the parameter mode

Just like other parameters, a cursor variable argument can have one of the following three modes:
IN
Can only be read by program
OUT
Can only be written to by program
IN OUT
Read/write in program
Remember that the value of a cursor variable is the reference to the cursor object and not the state of the cursor object. In other words, the value of a cursor variable does not change after you fetch from or close a cursor.
Only two operations, in fact, may change the value of a cursor variable change, that is, the cursor object to which the variable points:
·         An assignment to the cursor variable
·         An OPEN FOR statement
If the cursor variable already pointed to a cursor object, then the OPEN FOR wouldn't actually change the reference. It would simply change the query associated with the object.
The FETCH and CLOSE operations affect the state of the cursor object, but not the reference to the cursor object itself, which is the value of the cursor variable.
Here is an example of a program which has cursor variables as parameters:
PROCEDURE assign_curvar
   (old_curvar_in IN company.curvar_type,
    new_curvar_out OUT company.curvar_type)
IS
BEGIN
   new_curvar_out := old_curvar_in;
END;
This procedure copies the old company cursor variable to the new variable. The first parameter is an IN parameter because it appears only on the right-hand side of the assignment. The second parameter must be an OUT (or IN OUT) parameter, because its value is changed inside the procedure. Notice that the curvar_type is defined within the company package.

6.12.8 Cursor Variable Restrictions

Cursor variables are subject to the following restrictions; Oracle may remove some of these in future releases.
·         Cursor variables cannot be declared in a package since they do not have a persistent state.
·         You cannot use RPCs (Remote Procedure Calls) to pass cursor variables from one server to another.
·         If you pass a cursor variable as a bind or host variable to PL/SQL, you will not be able to fetch from it from within the server unless you also open it in that same server call.
·         The query you associate with a cursor variable in an OPEN-FOR statement cannot use the FOR UPDATE clause.
·         You cannot test for cursor variable equality, inequality, or nullity using comparison operators.
·         You cannot assign NULLs to a cursor variable.
·         Database columns cannot store cursor variable values. You will not be able to use REF CURSOR types to specify column types in statements to CREATE TABLEs or CREATE VIEWs.
·         The elements in a nested table, index-by table, or variable array (VARRAY) cannot store the values of cursor variables. You will not be able to use REF CURSOR types to specify the element type of a collection.
·         Cursor variables cannot be used with dynamic SQL (through use of the DBMS_SQL package).

Managing a Work Queue with SELECT FOR UPDATE

As discussed earlier, a cursor with a SELECT...FOR UPDATE syntax issues a row-level lock on each row identified by the query. I encountered a very interesting application of this feature while helping a client resolve a problem.
The client offers a distribution package which tracks warehouse inventory. The work queue screen assigns warehouse floor packers their next tasks. The packer opens the screen and requests a task. The screen finds the next unassigned task and assigns it to the packer. A task might involve collecting various products together for shipment or returning products to the shelf. Completion of this task can take anywhere between one and ten minutes. When the task is completed, the packer will commit the changes or close the screen, performing an implicit commit.
For the amount of time it takes a packer to finish the task, that record must be tagged as "assigned" so that no other packer is given the same job to do. The first attempt at implementing this feature involved the use of a status flag. Whenever a packer was assigned a task, the flag on that task was set to ASSIGNED and the task record committed. The screen then excludes that task from the work queue. The problem with this approach is that the status had to be committed to the database so that other users could see the new status. This commit not only interrupted the actual transaction in the screen, but also created a number of headaches:
·         What if the user never completes the task and exits the screen? The form would have to detect this scenario (and there are generally many ways to cancel/exit) and update the status flag to AVAILABLE, which involves yet another commit.
·         Worse yet, what if the database goes down while the user is performing the task? That task will disappear from the work queue until manual intervention resets the status.
My client needed a mechanism by which the task could be flagged as UNAVAILABLE without having to perform commits, build complex checks into the form, and develop crash-recovery guidelines. They needed a program that would step through each of the open tasks in priority until it found a task that was unassigned. The SELECT...FOR UPDATE construct proved to be the perfect answer, in combination with two queries against the task table -- an explicit cursor and an implicit cursor using a FOR UPDATE clause.
The function in the following example returns the primary key of the next unassigned task using a cursor against the task table to look through all open tasks in priority order. The tasks returned by this first cursor include those which are assigned but "in process" (and should therefore not be assigned again). For each task retrieved from this cursor, the function then tries to obtain a lock on that record using the FOR UPDATE...NOWAIT clause. If the SELECT statement cannot obtain a lock, it means that task is being handled by another packer. So the function fetches the next task and tries, once again, to obtain a lock, continuing on in this fashion until a free task is found or the last task is fetched.
Notice that the next_task function does not perform any commits, so it doesn't have to do any kind of complicated clean-up. It simply requests the lock and returns the primary key for that task. The calling program can then offer this task to the packer who will issue the commit, freeing the lock, when she or he is done with the task:
/* Filename on companion disk: selupdt.sf */
FUNCTION next_task RETURN task.task_id%TYPE
IS
   /* Cursor of all open tasks, assigned and unassigned */
   CURSOR task_cur IS
      SELECT task_id
        FROM task
       WHERE task_status = 'OPEN'
       ORDER BY task_priority, date_entered DESC;
 
   /* The record for the above cursor */
   task_rec task_cur%ROWTYPE;
   /*
   || An exception for error ORA-00054:
   || "resource busy and acquire with NOWAIT specified"
   */
   record_locked EXCEPTION
   PRAGMA EXCEPTION_INIT (record_locked, -54);
   /*
   || Variables which determine whether function should continue
   || to loop through the cursor's records.
   */
   found_unassigned_task BOOLEAN := FALSE;
   more_tasks BOOLEAN := TRUE;
 
   /* The primary key of the unassigned task to be returned */
   return_value task.task_id%TYPE := NULL;
BEGIN
   /* Open the cursor and start up the loop through its records */
   OPEN task_cur;
   WHILE NOT found_unassigned_task AND more_tasks
   LOOP
      /* Fetch the next record. If nothing found, we are done */
      FETCH task_cur INTO task_rec;
      more_tasks := task_cur%FOUND;
      IF more_tasks
      THEN
         /*
         || A record was fetched. Create an anonymous block within
         || the function so that I can trap the record_locked
         || exception and still stay inside the cursor loop.
         */
         BEGIN
            /* Try to get a lock on the current task */
            SELECT task_id INTO return_value
              FROM task
              WHERE task_id = task_rec.task_id
             FOR UPDATE OF task_id NOWAIT;
            /*
            || If I get to this line then I was able to get a lock
            || on this particular task. Notice that the SELECT INTO
            || has therefore already set the function's return value.
            || Now set the Boolean to stop the loop.
            */
            found_unassigned_task := TRUE;
         EXCEPTION
            WHEN record_locked
            THEN
               /* Record was already locked, so just keep on going */
               NULL;
         END;
      END IF;
   END LOOP;
   /*
   || Return the task id. Notice that if an unassigned task was NOT
   || found, I will simply return NULL per declaration default.
   */
   CLOSE task_cur;
   RETURN return_value;
EXCEPTION
   /*
   || General exception handler for the function: if an error occurred,
   || then close the cursor and return NULL for the task ID.
   */
   WHEN OTHERS
   THEN
      CLOSE task_cur;
      RETURN NULL;
END; 
 

RESTRICT_REFERENCES pragma

A stored function can exist as a standalone function or as a function in a package. For standalone functions, the Oracle Server automatically determines whether it is callable in SQL. It will, for example, reject your SQL statement if it uses a function that issues an UPDATE statement. The situation with packaged functions is a bit more complicated.
As noted earlier, the specification and body of a package are distinct; a specification can exist even before its body. For this and other reasons, the Oracle Server cannot automatically determine (when you execute your SQL) that a packaged function is valid for SQL execution. Instead, you must state explicitly the "purity level" of a function in a package with the RESTRICT_REFERENCES pragma. The Oracle Server then determines at compile time (of the package body) if the function violates the purity level, and raise a compilation error if this is the case. Once the package is compiled, the functions for which assertions have been made can be called in SQL.
Let's explore the specific syntax required to achieve this effect.
A pragma is a special directive to the PL/SQL compiler. If you have ever created a programmer-defined, named exception, you have already encountered your first pragma. In the case of the RESTRICT_REFERENCES pragma, you are telling the compiler the purity level you believe your function meets or exceeds.
You need a separate PRAGMA statement for each packaged function you wish to use in a SQL statement, and it must come after the function declaration in the package specification (you do not specify the pragma in the package body).
To assert a purity level with the pragma, use the following syntax:
PRAGMA RESTRICT_REFERENCES 
   (function_name, WNDS [, WNPS] [, RNDS] [, RNPS])
where function_name is the name of the function whose purity level you wish to assert, and the four different codes have the following meanings:
Purity Code
Description
WNDS
Stands for "Writes No Database State." Asserts that the function does not modify any database tables.
WNPS
Stands for "Writes No Package State." Asserts that the function does not modify any package variables.
RNDS
Stands for "Reads No Database State." Asserts that the function does not read any database tables.
RNPS
Stands for "Reads No Package State." Asserts that the function does not read any package variables.
Notice that only the WNDS level is mandatory in the pragma. That is consistent with the restriction that stored functions in SQL may not execute an UPDATE, INSERT, or DELETE statement. All other states are optional. You can list them in any order, but you must include the WNDS argument. No one argument implies another argument. For example, I can write to the database without reading from it. I can read a package variable without writing to a package variable.
Here is an example of two different purity level assertions for functions in the company_financials package:
PACKAGE company_financials
IS
   FUNCTION company_type (type_code_in IN VARCHAR2) 
      RETURN VARCHAR2;
 
   FUNCTION company_name (company_id_in IN company.company_id%TYPE) 
      RETURN VARCHAR2;
 
   PRAGMA RESTRICT_REFERENCES (company_type, WNDS, RNDS, WNPS, RNPS);
   PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS);
END company;
In this package, the company_name function does read from the database to obtain the name for the specified company. Notice that I placed both pragmas together at the bottom of the package specification. The pragma does not need to immediately follow the function specification. I also went to the trouble of specifying the WNPS and RNPS arguments for both of the functions. Oracle Corporation recommends that you assert the highest possible purity levels so that the compiler will never reject the function unnecessarily.
I have found, on the other hand, that the PL/SQL compiler does at times reject my purity level assertions when there does not seem to be any apparent violation. You may at times have to retreat to the minimal WNDS assertion simply to get your package to compile.

1.6.5.2 Asserting the purity level of the initialization section

If your package contains an initialization section (executable statements after a BEGIN statement in the package body), you must also assert the purity level of that section. The initialization section is executed automatically the first time any package object is referenced. So if a packaged function is used in a SQL statement, it will trigger execution of that code. If the initialization section modifies package variables or database information, the compiler needs to know about that through the pragma.
You can assert the purity level of the initialization section either directly or indirectly. To use a direct assertion, you use this variation of the pragma RESTRICT_REFERENCES:
PRAGMA RESTRICT_REFERENCES 
   (package_name, WNDS, [, WNPS] [, RNDS] [, RNPS])
Instead of specifying the name of the function, you include the name of the package itself, followed by all the applicable state arguments. In the following argument I assert only WNDS and WNPS because the initialization section reads data from the configuration table and also reads the value of a global variable from another package (session_pkg.user_id).
PACKAGE configure
IS
   PRAGMA RESTRICT_REFERENCES (configure, WNDS, WNPS);
   user_name VARCHAR2(100);
END configure;
 
PACKAGE BODY configure
IS
BEGIN
   SELECT lname || ', ' || fname INTO user_name
     FROM user_table
    WHERE user_id = session_pkg.user_id;
END configure;
Why can I assert the WNPS even though I do write to the user_name package variable? Answer: It's a variable from this same package, so the action is not considered a side effect.
You can also assert the purity level of the package's initialization section by allowing the compiler to infer that level from the purity level(s) of all the pragmas for individual functions in the package. In the following version of the company package, the two pragmas for the functions allow the Oracle Server to infer a combined purity level of RNDS, WNPS for the initialization section. This means that the initialization section cannot read from the database and cannot write to a package variable.
PACKAGE company
IS
   FUNCTION get_company (company_id_in IN VARCHAR2) 
      RETURN company%ROWTYPE;
 
   FUNCTION deactivate_company (company_id_in IN company.company_id%TYPE)
      RETURN VARCHAR2;
 
   PRAGMA RESTRICT_REFERENCES (get_company, RNDS, WNPS);
   PRAGMA RESTRICT_REFERENCES (deactivate_name, WNPS);
END company;
Generally, you are probably better off providing an explicit purity level assertion for the initialization section. This makes it easier for those responsible for maintaining the package to understand both your intentions and your understanding of the package.

 

The PRAGMA Keyword

The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, to the compiler. Pragmas are processed at compile time; they do not execute during runtime.
A pragma is a special instruction to the compiler. Also called a pseudoinstruction, the pragma doesn't change the meaning of a program. It simply passes information to the compiler. It is very similar, in fact, to the tuning hints you can embed in a SQL statement inside a block comment.
PL/SQL offers the following pragmas:
EXCEPTION_INIT
Tells the compiler to associate a particular error number with an identifier you have declared as an exception in your program. See Chapter 8, Exception Handlers for more information.
RESTRICT_REFERENCES
Tells the compiler the purity level (freedom from side effects) of a packaged program. See Chapter 17, Calling PL/SQL Functions in SQL for more information.
SERIALLY_REUSABLE
New to PL/SQL8. Tells the PL/SQL runtime engine that package-level data should not persist between references to that data. See Chapter 25, Tuning PL/SQL Applications for more information.
The syntax for using the PRAGMA keyword is as follows:
PRAGMA <instruction>;
where <instruction> is a statement providing instructions to the compiler. You would call EXCEPTION_INIT as follows:
DECLARE
   no_such_sequence EXCEPTION;
   PRAGMA EXCEPTION_INIT (no_such_sequence, -2289);
BEGIN
   ...
END;

 

Working with PL/SQL Tables

The remainder of this chapter provides you with lots of examples of ways to use PL/SQL tables in your applications.

10.9.1 Transferring Database Information to PL/SQL Tables

You cannot use a SQL SELECT statement to transfer data directly from a database table to a PL/SQL table. You need to take a programmatic approach. A cursor FOR loop usually makes the most sense for this process, which requires the following steps:
1.      Define a PL/SQL table TYPE for each datatype found in the columns of the database table.
2.      Declare PL/SQL tables which will each receive the contents of a single column.
3.      Declare the cursor against the database table.
4.      Execute the FOR loop. The body of the loop will contain a distinct assignment of one column into one PL/SQL table.
In PL/SQL Release 2.3, this process would be much simpler. You could define a PL/SQL table with the same structure as the database table by creating a table-based record. Prior to that release, unfortunately, you need a separate PL/SQL table for each column. You do not, on the other hand, need a separate table TYPE for each column. If you have two date columns, for example, you can declare two separate PL/SQL tables both based on the same TYPE.
In the following example I load the company ID, incorporation date, and filing date from the database table to three different PL/SQL tables. Notice that there are only two types of PL/SQL tables declared:
/* Filename on companion disk: db2tab1.sql (see db2tab2.sql for the PL/SQL Release 2.3 version of same transfer) */
DECLARE
   /* The cursor against the database table. */
   CURSOR company_cur
   IS
       SELECT company_id, incorp_date, filing_date FROM company;
 
   /* The PL/SQL table TYPE and declaration for the primary key. */
   TYPE company_keys_tabtype IS
      TABLE OF company.company_id%TYPE NOT NULL
      INDEX BY BINARY_INTEGER;
   company_keys_table primary_keys_tabtype;
 
  /* Sincle PL/SQL table TYPE for two different PL/SQL tables. */
   TYPE date_tabtype IS
      TABLE OF DATE
      INDEX BY BINARY_INTEGER;
   incorp_date_table date_tabtype;
   filing_date_table date_tabtype;
 
   /* Variable to keep track of number of rows loaded. */
   num_company_rows BINARY_INTEGER := 0;
BEGIN
   /* The cursor FOR loop */
   FOR company_rec IN company_cur
   LOOP
      /* Increment to the next row in the two, coordinated tables. */
      num_company_rows := num_company_rows + 1;
 
      /* Set the row values for ID and dates. */
      company_keys_table (num_company_rows) := company_rec.company_id;
      incorp_date_table (num_company_rows) := company_rec.incorp_date;
      filing_date_table (num_company_rows) := company_rec.filing_date;
 
   END LOOP;
END;

10.9.2 Data-Smart Row Numbers in PL/SQL Tables

As I've mentioned, one of the most interesting and unusual aspects of the PL/SQL table is its sparseness. I can have a value in the first row and in the 157th row of the table, with nothing in between. This feature is directly related to the fact that a PL/SQL table is unconstrained. Because there is no limit on the number of rows in a table, PL/SQL does not set aside the memory for that table at the time of creation, as would normally occur with an array.
When you use the PL/SQL table to store and retrieve information sequentially, this sparse quality doesn't have any real significance. The ability to store data nonsequentially can, however, come in very handy. Because the row number does not have to be sequentially generated and used, it can represent data in your application. In other words, it can be "data-smart."
Suppose you want to use a PL/SQL table to store the text of messages associated with numeric error codes. These error codes are patterned after Oracle error codes, with ranges of values set aside for different aspects of the application: 1000-1999 for employee-related errors, 5000-5999 for company-related errors, etc. When a user action generates an error, the error number is passed to a procedure which then looks up and displays the message. By storing this information in PL/SQL tables, you avoid a lookup against the remote database.
Let's take a look at sequential and indexed access to implement this functionality.

10.9.2.1 Sequential, parallel storage

One possible way to implement this procedure is to create two tables: one that holds the error codes (stored sequentially in the table) and another that holds the messages (also stored sequentially). When an error is encountered, the procedure scans sequentially through the PL/SQL table of codes until it finds a match. The row in which the code is found is also the row in the PL/SQL message table; it uses the row to find the message and then displays it. Figure 10.2 shows the correlation between these two tables.

Figure 10.2: Using sequential access to correlate contents of two tables

Figure 10.2
The code needed to implement this algorithm is shown in the following procedure. The procedure assumes that the two PL/SQL tables have already been loaded with data. The error_pkg.last_row variable is the last row containing an error code:
/* Filename on companion disk: seqretr.sp */
PROCEDURE display_error (errcode_in IN NUMBER)
IS
   matching_row BINARY_INTEGER := 1;
   keep_searching BOOLEAN := error_pkg.last_row > 0;
BEGIN
   WHILE keep_searching
   LOOP
      /* Does the current row match the specified error code? */
      IF error_pkg.error_codes_table (matching_row) = errcode_in
      THEN
         DBMS_OUTPUT.PUT_LINE
            (error_pkg.error_messages_table (matching_row));
         keep_searching := FALSE;
      ELSE
         /* Move to the next error code in the table */
         matching_row := matching_row + 1;
         keep_searching := matching_row <= error_pkg.last_row;
      END IF;
   END LOOP;
END;
A straightforward, sensible approach, right? Yes and no. Yes, it is straightforward. No, in the context of the PL/SQL table, it is not sensible. This module insists on performing a sequential scan when such a step is not necessary.

10.9.2.2 Using the index as an intelligent key

A much simpler way to accomplish this same task is to use the error code itself as the primary key value for the row in the error messages table. Then I need only one table -- to hold the error messages (see Figure 10.3).

Figure 10.3: Using indexed access to retrieve value with intelligent key row

Figure 10.3
Instead of matching the error code and then using the primary key to locate the corresponding message, the error code is itself the index into the PL/SQL table. By using a single table and data-smart values for the primary key, the display_error procedure boils down to the code shown below:
/* Filename on companion disk: indretr.sp */
PROCEDURE display_error (errcode_in IN NUMBER) IS
BEGIN
   /*
   || Deceptively simple: use the error code as the row, retrieve
   || the message, and display it. All in one statement!
   */
   DBMS_OUTPUT.PUT_LINE
            (error_pkg.error_messages_table (errcode_in));
EXCEPTION
   /*
   || Just in case an undefined error code is passed to the
   || procedure, trap the failure in the exception section.
   */
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE
          ('No match found for error code = ' || TO_CHAR (errcode_in));
END;
It has taken me a while to fully internalize the difference between a PL/SQL table and an array. I have had to go back and rewrite several packages and procedures once I realized that I had done it again, treating the PL/SQL table like a sequential access data structure.
So if you ever find yourself reading sequentially through a PL/SQL table, take a step back and consider what data you need to obtain and how it is being stored. Sometimes you do need to store data sequentially (when you use a PL/SQL table, for example, to implement a stack data structure). Frequently, however, you can simplify your life and your code by using data-smart values in your PL/SQL table.

10.9.3 Displaying a PL/SQL Table

When you work with PL/SQL tables, you often want to verify the contents of the table. The usual verification method is to display the contents of each row using DBMS_OUTPUT. This sounds like a simple enough task. In the most basic scenario where you have a sequentially filled table, the code is indeed straightforward.
The following procedures shows the small amount of code required to display a table which has rows 1 through n defined, where n is passed as a parameter to the procedure. The procedure displays a VARCHAR2 table; to display DATE or NUMBER tables, you simply need to use TO_CHAR to convert the value in the call to PUT_LINE:
/* Filename on companion disk: disptab1.sp */
PROCEDURE display_table
   (table_in IN <the_table_type>, -- Placeholder for real table type.
    number_of_rows_in IN INTEGER)
IS
BEGIN
   /* For each row in the table ... */
   FOR table_row IN 1 .. number_of_rows_in
   LOOP
      /* Display the message, including the row number */
      DBMS_OUTPUT.PUT_LINE
         ('Value in row ' || TO_CHAR (table_row) || ': ' ||
          table_in (table_row));
   END LOOP;
END;
To put this display_table module to use, you will need to create a different version of this procedure for each different type of table, because the table is passed as a parameter. That table's type must be declared in the parameter list in place of the <the_table_type> text.
Of course, not all PL/SQL tables can be filled in ways which are displayed as easily as the one shown above. To start with, the display_table procedure makes many assumptions about its table profile (although very few tables actually fit this profile). These include the following:
·         The first defined row of the table is row one. The FOR loop always starts at one.
·         All rows between one and number_of_rows_in are defined. There is no exception handler for NO_DATA_FOUND.
·         The number_of_rows_in is a positive number. The loop does not even consider the possibility of negative row numbers.
Very few PL/SQL tables actually fit this profile. Even a traditional, sequentially filled table might start its rows at some arbitrary value; a PL/SQL table might be sparsely filled; you might know the starting row (lowest value) and the ending row (maximum value), but not really know which of the rows between those end points are defined. A table with the Oracle error codes would have all of these characteristics.

10.9.3.1 A flexible display_table procedure

It is possible to build a version of display_table which takes into account all of these variations in a PL/SQL table's structure. For example:
·         The program must be supplied with both a starting and an ending row for the scan through the table.
·         The program also needs to be told how many times it should encounter and handle an undefined row as it scans the table.
·         display_table needs a parameter which specifies the increment by which it loops through the table. This increment can be negative, which would allow you to scan in reverse through the rows of the table.
By incorporating all of this input, the header for display_table becomes:
PROCEDURE display_table
   (table_in IN <the_table_type>,
    end_row_in IN INTEGER ,
    start_row_in IN INTEGER := 1,
    failure_threshold_in IN INTEGER := 0,
    increment_in IN INTEGER := +1)
where the parameters are defined as:
table_in
The table to be displayed. Again, you would need a different version of this program for each different table type you want to display.
end_row_in
The last row that is defined in the table. If sequentially filled from row 1, this value would simply be the maximum number of rows.
start_row_in
The starting row defined in the table. The default is 1.
failure_threshold_in
The number of times the procedure will raise the NO_DATA_FOUND exception before it stops scanning through the table. The default is zero, which means that the first time it tries to access an undefined row it will stop.
increment_in
The amount by which the row counter is incremented as the procedure scans through the table. The default is +1. If you know that every 15th row is defined, you can pass 15 and avoid the NO_DATA_FOUND exceptions. If your rows are negative, as with Oracle error codes, specify -1 for the increment and the procedure will then scan backwards through the table.
This version of display_table shown in the following example displays both sequentially filled and sparsely filled PL/SQL tables. When I use the new display_table to view the contents of a sequentially filled table, the call to the procedure looks exactly the same as in the first version:
display_table (customer_tab, num_customers);
If I store the Oracle error codes in my table, then I can take advantage of all these different parameters to view only the errors dealing with the date functions (-01800 through -01899) with the following command:
display_table (ora_errors_tab, -1899, -1800, 100, -1);
My end row is -1899 and my start row is -1800. I allow the procedure to access up to 100 undefined rows, because not all of these values are currently defined. I know that this is enough because the full range is only 100. Finally, I tell display_table to read backwards through the table, as is appropriate given the negative values of the rows.

10.9.3.2 Examples of display_table output

To test the display_table procedure I created a package named dt which declared a VARCHAR2 table type named string_tabletype and defined the procedure. I then built the SQL*Plus script shown in the example below. Notice that I use three substitution variables (&1, &2, and &3):
DECLARE
   t dt.string_tabletype;
BEGIN
   t(1) := 'hello';
   t(2) := 'world!';
   t(11) := 'I hope';
   t(21) := 'we make it';
   t(22) := 'to the year';
   t(75) := '2000.';
   dt.display_table (t, &1, &2, &3);
END;
/
Here are some of the results of my test scripts:
SQL> start disptab 100 1 100
Value in row 1: hello
Value in row 2: world!
Value in row 11: I hope
Value in row 21: we make it
Value in row 22: to the year
Value in row 75: 2000.
 
SQL> start disptab 100 1 50
Value in row 1: hello
Value in row 2: world!
Value in row 11: I hope
Value in row 21: we make it
Value in row 22: to the year
Exceeded threshold on undefined rows in table.
 
SQL> start disptab 2 1 0
Value in row 1: hello
Value in row 2: world!
 
SQL> start disptab 3 1 0
Value in row 1: hello
Value in row 2: world!
Exceeded threshold on undefined rows in table.
 
SQL> start disptab 50 10 50
Value in row 11: I hope
Value in row 21: we make it
Value in row 22: to the year

10.9.3.3 Implementation of display_table

The first argument in display_table is a PL/SQL table. Because there is no such thing as a generic PL/SQL table structure, you will need to create a version of this procedure for each of your tables. Alternatively, you could create a generic program which handles all the logic in this program which does not rely on the specific table. Then each of your PL/SQL table-specific versions could simply call that generic version.
/* Filename on companion disk: disptab2.sp */
PROCEDURE display_table
   (table_in IN <the_table_type>,
    end_row_in IN INTEGER ,
    start_row_in IN INTEGER := 1,
    failure_threshold_in IN INTEGER := 0,
    increment_in IN INTEGER := +1)
IS
   /* The current row displayed as I scan through the table */
   current_row INTEGER := start_row_in;
 
   /* Tracks number of misses, compared to threshold parameter. */
   count_misses INTEGER := 0;
 
   /* Used in WHILE loop to control scanning thru table. */
   within_threshold BOOLEAN := TRUE;
 
   /*----------------------- Local Module ------------------------*/
   || Determine if specified row is within range. I put this
   || into a function because I need to see which direction I
   || am scanning in order to determine whether I'm in range.
   */
   FUNCTION in_range (row_in IN INTEGER) RETURN BOOLEAN IS
   BEGIN
      IF increment_in < 0
      THEN
         RETURN row_in >= end_row_in;
      ELSE
         RETURN row_in <= end_row_in;
      END IF;
   END;
BEGIN
   /* The increment cannot be zero! */
   IF increment_in = 0
   THEN
      DBMS_OUTPUT.PUT_LINE
            ('Increment for table display must be non-zero!');
   ELSE
      /*
      || Since I allow the user to pass in the amount of the increment
      || I will switch to a WHILE loop from the FOR loop. I keep
      || scanning if (1) have not reached last row and (2) if I have
      || not run into more undefined rows than allowed by the
      || threshold parameter.
      */
      WHILE in_range (current_row) AND within_threshold
      LOOP
         /*
         || I place call to PUT_LINE within its own anonymous block.
         || This way I can trap a NO_DATA_FOUND exception and keep on
         || going (if desired) without interrupting the scan.
         */
         BEGIN
            /* Display the message, including the row number */
            DBMS_OUTPUT.PUT_LINE
               ('Value in row ' || TO_CHAR (current_row) || ': ' ||
                table_in (current_row));
 
            /* Increment the counter as specified by the parameter */
            current_row := current_row + increment_in;
 
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               /*
               || If at the threshold then shut down the WHILE loop by
               || setting the Boolean variable to FALSE. Otherwise,
               || increment the number of misses and current row.
               */
               within_threshold := count_misses < failure_threshold_in;
               IF within_threshold
               THEN
                  count_misses := count_misses + 1;
                  current_row := current_row + increment_in;
              END IF;
         END;
      END LOOP;
      /*
      || If I stopped scanning because of undefined rows, let the
      || user know.
      */
      IF NOT within_threshold
      THEN
         DBMS_OUTPUT.PUT_LINE
            ('Exceeded threshold on undefined rows in table.');
      END IF;
   END IF;
END;
By spending the time to think about the different ways a developer might want to display a table and the different kinds of tables to be displayed, and by adding the parameters necessary to handle these scenarios, I have transformed a very simple and limited procedure into a generic and useful program. While the more generic code is significantly more complex than that in the original display_table, I have to write it (and test it) only once. From then on, I simply substitute an actual table type.

10.9.4 Building Traditional Arrays with PL/SQL Tables

As I've mentioned, neither PL/SQL nor any other component of the Oracle product set supports true arrays. You cannot, for example, declare a two-dimensional array which is manipulated with statements such as the following:
company (2,3) := '100 Main St';
min_profits := financial_matrix (profit_row, min_column);
For years developers have complained about the lack of support for arrays. Although tables serve some of the same purposes as arrays, they are not equivalent. You can use a PL/SQL table as an array, but only one that is single-dimensional. If you need to make use of the more common two-dimensional array, such as a 10 в 10 array composed of 100 cells, then a PL/SQL table -- all on its own -- will not do the trick. You can, however, build a layer of code which will emulate a traditional n в m array based on the PL/SQL table data structure.
NOTE: This implementation relies on the PL/SQL package structure, which is covered in Chapter 16, Packages. If you are not familiar yet with packages, you may want to read through that chapter before diving into this exploration of array emulation.

10.9.4.1 Obstacles to implementing arrays

The first time I tried to implement such an array structure with PL/SQL tables, I gave up, thinking that it simply wasn't possible. My strategy had been to create a table for each of the n columns in the array. A 2 в 3 array would, in other words, consist of three different tables of the same type, as shown in Figure 10.4. The problem with this approach is that I could not dynamically generate the name of a PL/SQL table at runtime.
Suppose I supplied you with a function in which you specified the number of rows and columns in your array. You would expect to have a pointer to the array of that size returned to you. I can certainly return to you a pointer to a single PL/SQL table; it would simply be declared at the start of the function. If the array was made up of an arbitrary number of tables, however, I could not declare each of these as they were needed.

Figure 10.4: Three tables supporting a 2 в 3 array

Figure 10.4
I was about to give up when I realized that, oddly enough, the elements of the PL/SQL table which make it so different from an array -- that it is both unconstrained and sparse -- allow me to use a single PL/SQL table to implement the traditional n в m array, where n and m are virtually any positive integers. Because the PL/SQL table has no (practical) size limitation and I can use whichever rows in the table I desire, I can spread the m different columns of n rows across the expanse of the PL/SQL table. In other words, I can partition the single PL/SQL table so that it contains all the cells of a traditional array.

10.9.4.2 Partitioning a PL/SQL table to store an array

To understand how this partitioning works, consider a 3 в 4 array. This array contains a total of 12 cells. The cells would be spread among the rows of a PL/SQL table as shown in Figure 10.5. Using the distribution in the figure, we can see that cell (3,3) would be stored in row 9 of the table, that cell (2,4) would be stored in row 11, and so on.

Figure 10.5: Distribution of 3 в 4 array cells in PL/SQL table

Figure 10.5
The general formula which converts the row and column of an array cell to the corresponding PL/SQL table row is this:
table_row := (cell_column - 1) * number_of_rows_in_array + cell_row;
where (cell_row, cell_column) is the cell in the array and number_of_rows_in_array is the total number of rows in the array.

10.9.4.3 Features of the array package

The array package I have developed (shown later in this section) implements traditional n в m arrays using PL/SQL tables. This package supports arrays of numeric values only. You would need to create an overloaded version of these same modules for character, date, and other values.
The following shows the modules and capabilities offered by the array package:
array.make
Make or declare an array with specified numbers of rows and columns. You can optionally specify the default value you want placed in each cell.
array.cell
Obtain the value of any cell in the array.
array.change
Change the value of any cell in the array.
array.erase
Erase the array from memory.
array.display
Display the contents of the array. You can display the table contents in array style (matrix format) or inline style (one cell per line).
The package also provides two functions to return the number of rows and columns in the array (number_of_rows and number_of_columns, respectively). It does not, on the other hand, give programmers direct access to the variables which store these values. Instead, I hide the variables themselves in the package body and place the functions in the specification. There are two reasons for this layer of code:
·         You cannot access stored package variables and PL/SQL Version 2 data structures such as PL/SQL tables directly from PL/SQL Release 1.1. The Version 1.1 layer cannot parse such references (see Appendix B, Calling Stored Procedures from PL/SQL Version 1.1). You can, however, call stored package functions from within Oracle Developer/2000.
·         It is always better to hide the way you implement functionality, including variables names, data structures, and algorithms. By not exposing the details (in this case, the names of the variables which store this information), I can change the underlying implementation without affecting the way programmers use the data.
Most of the code needed to support arrays is straightforward. Oddly enough, the most complicated module in this package is the display procedure; its complexity is derived from the flexibility it offers. You can display the contents of an array in linestyle (one cell per line) or array style (all cells in each row on the same line, like a spreadsheet). You can also ask to display only selected columns and rows.

10.9.4.4 Examples using array package

The following four examples show how the array package is used.
·         Create a 10 в 10 array with values all set to 0:
array.make (10, 10, 0);
·         Set all the values in the first column of the array to 15:
FOR row_index IN 1 .. array.row_count
LOOP
   array.change (row_index, 1, 15);
END LOOP;
·         Set the values in the third row to the values in the fifth row:
FOR col_index IN 1 .. array.column_count
LOOP
   array.change (3, col_index, array.cell (5, col_index));
END LOOP;
·         Create and display contents of arrays:
·         SQL> execute array.make (10,10,25);
·          
·         PL/SQL procedure successfully completed.
·          
·         SQL>   execute array.display(4,6,2,4);
·         Row       Column 2  Column 3  Column 4
·         4         25        25        25
·         5         25        25        25
·         6         25        25        25
·          
·         SQL>  execute array.display(4,6,2,4,'line');
·         Cell (4,2): 25
·         Cell (4,3): 25
·         Cell (4,4): 25
·         Cell (5,2): 25
·         Cell (5,3): 25
·         Cell (5,4): 25
·         Cell (6,2): 25
·         Cell (6,3): 25
Cell (6,4): 25
There are many possibilities for expanding the capabilities of the array package. You could, for example, offer a module which performs scalar operations against the specified column(s) and row(s) of the array, such as multiplying all cells in the first column by a value. If you implement a version of the array package which supports more than one array at a time, you could build array-level, aggregate operations, such as "multiply two arrays together" or "subtract one array from another," and so on.
I hope that you can see from this section that even if the current version of PL/SQL does not offer every single feature you might want or need, there is often a way to emulate the desired feature. The array package is not nearly as convenient or efficient as true, native arrays would be, but if you have a need right now for an array in PL/SQL Version 2, the package implementation is hard to beat!

10.9.4.5 Limitations in array implementation

There is one major limitation to the implementation of arrays in this package. You can use only one array at a time from this package. The reason for this restriction is that I need to keep track of the number of rows and columns in the array. To make an association between the array (a PL/SQL table) and those values, I would need to ask you for a name for this array and then create named globals with the number of rows and columns. I avoid those complications by "burying" the structure of the array inside the package.
On the positive side, there are several advantages to hiding the table. One is that you have an array which is very easy to use. You never have to declare a table yourself. You simply ask to create or make the array and then you can access the cells.
Another advantage of hiding the table is that you don't have to pass it as a parameter when you call the modules. A PL/SQL table parameter could harm performance, because all the rows of the table are copied into and out of local memory structures for manipulation in that module. In the array package, the number_array table is declared at the package level so it is available to all modules in the package without having to be passed as a parameter.
One final advantage is that you can call this array package (stored in the database) from within the Oracle Developer/2000 tools. If the table were exposed, then the array could only be used in a PL/SQL Version 2 environment. Wow! When I decided to build it this way, I had no idea it would make this much sense.

10.9.4.6 The array package specification

The package specification lists the modules which programmers can call to create and manipulate an array with PL/SQL tables. Notice that the statements in the specification only show the header information: module name and parameters. The code behind these headers is found in the package body (next section):
/* Filename on companion disk: array.sps */
PACKAGE array
IS
   /* Returns the number of rows in the array */
   FUNCTION row_count RETURN INTEGER;
 
   /* Returns the number of columns in the array */
   FUNCTION column_count RETURN INTEGER;
 
   /* Create an array */
   PROCEDURE make
      (num_rows_in IN INTEGER := 10,
       num_columns_in IN INTEGER := 1,
       initial_value_in IN NUMBER := NULL,
       conflict_action_in IN VARCHAR2 := 'OVERWRITE');
 
   /* Return the value in a cell */
   FUNCTION cell (row_in IN INTEGER, col_in IN INTEGER)
      RETURN NUMBER;
 
   /* Change the value in a cell */
   PROCEDURE change
      (row_in IN INTEGER, col_in IN INTEGER, value_in IN NUMBER);
 
   /* Erase the array */
   PROCEDURE erase;
 
   /* Display the array */
   PROCEDURE display
      (start_row_in IN INTEGER := 1,
       end_row_in IN INTEGER := row_count,
       start_col_in IN INTEGER := 1,
       end_col_in IN INTEGER := column_count,
       display_style_in IN VARCHAR2 := 'ARRAY');
 
END array;

10.9.4.7 The array package body

The package body provides the code which implements the modules listed in the array package specification. The longest module in this bunch is the display procedure. Because this code is basically the same as that shown in Section 10.9.3, "Displaying a PL/SQL Table". I have not repeated it here. However, the full package body is found on the companion disk.
/* Filename on companion disk: array.spb */ 
PACKAGE BODY array
IS
   /*------------------------Private Variables ----------------------*/
 
   /* The number of rows in the array */
   number_of_rows INTEGER := NULL;
 
   /* The number of columns in the array */
   number_of_columns INTEGER := NULL;
 
   /* The generic table structure for a numeric table */
   TYPE number_array_type IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
 
   /* The actual table which will hold the array */
   number_array number_array_type;
 
   /* An empty table used to erase the array */
   empty_array number_array_type;
 
   /*------------------------Private Modules ----------------------*/
 
   FUNCTION row_for_cell (row_in IN INTEGER, col_in IN INTEGER)
      RETURN INTEGER
   /*
   || Returns the row in the table that stores the value for
   || the specified cell in the array.
   */
   IS
   BEGIN
      RETURN (col_in - 1) * number_of_rows + row_in;
   END;
 
   /*------------------------Public Modules ----------------------*/
 
   FUNCTION row_count RETURN INTEGER IS
   BEGIN
      RETURN number_of_rows;
   END;
 
   FUNCTION column_count RETURN INTEGER IS
   BEGIN
      RETURN number_of_columns;
   END;
 
   PROCEDURE make
      (num_rows_in IN INTEGER := 10,
       num_columns_in IN INTEGER := 1,
       initial_value_in IN NUMBER := NULL,
       conflict_action_in IN VARCHAR2 := 'OVERWRITE')
   /*
   || Create an array of the specified size, with the initial
   || value. If the table is already in use, it will be erased
   || and then re-made only if the conflict action is the
   || default value above.
   */
   IS
   BEGIN
      /*
      || If number_of_rows is NOT NULL, then the array is
      || already in use. If the conflict action is the
      || default or OVERWRITE, then erase the existing
      || array.
      */
      IF number_of_rows IS NOT NULL AND
         UPPER (conflict_action_in) = 'OVERWRITE'
      THEN
         erase;
      END IF;
      /*
      || Only continue now if my number of rows is NULL.
      || If it has a value, then table is in use and user
      || did NOT want to overwrite it.
      */
      IF number_of_rows IS NULL
      THEN
         /* Set the global variables storing size of array */
         number_of_rows := num_rows_in;
         number_of_columns := num_columns_in;
         /*
         || A PL/SQL table's row is defined only if a value
         || is assigned to that row, even if that is only a
         || NULL value. So to create the array, I will simply
         || make the needed assignments. Remember: I use a single
         || table, but segregate distinct areas of the table for each
         || column of data. I use the row_for_cell function to
         || "space out" the different cells of the array across
         || the table.
         */
         FOR col_index IN 1 .. number_of_columns
         LOOP
            FOR row_index IN 1 .. number_of_rows
            LOOP
               number_array (row_for_cell (row_index, col_index))
                  := initial_value_in;
            END LOOP;
         END LOOP;
      END IF;
   END;
 
   FUNCTION cell (row_in IN INTEGER, col_in IN INTEGER)
      RETURN NUMBER
   /*
   || Retrieve the value in a cell using row_for_cell.
   */
   IS
   BEGIN
      RETURN number_array (row_for_cell (row_in, col_in));
   END;
 
   PROCEDURE change
      (row_in IN INTEGER, col_in IN INTEGER, value_in IN NUMBER)
   /*
   || Change the value in a cell using row_for_cell.
   */
   IS
   BEGIN
      number_array (row_for_cell (row_in, col_in)) := value_in;
   END;
 
   PROCEDURE erase
   /*
   || Erase a table by assigning an empty table to a non-empty
   || array. Then set the size globals for the array to NULL.
   */
   IS
   BEGIN
      number_array := empty_array;
      number_of_rows := NULL;
      number_of_columns := NULL;
   END;
 
   PROCEDURE display
      (start_row_in IN INTEGER := 1,
       end_row_in IN INTEGER := row_count,
       start_col_in IN INTEGER := 1,
       end_col_in IN INTEGER := column_count,
       display_style_in IN VARCHAR2 := 'ARRAY')
   IS
   BEGIN
   /*
   || See code on disk. This repeats, more or less, the code shown
   || above to display a table.
   */
   END display;
 
END array;
 

10.9.5 Optimizing Foreign Key Lookups with PL/SQL Tables

Something you'll do again and again in your client-server applications is look up the name or description of a foreign key from a database table that is resident on the server. This lookup often occurs, for example, in the Post-Query trigger of Oracle Forms applications. The base table block contains a database item for the company_id and a non-database item for company_name. What if you have to process a number of different records in your employee table? The SQL processing is very inefficient. When a record is queried from the database, the Post-Query trigger executes an additional SELECT statement to obtain the name of the company, as shown in the following example:
DECLARE
   CURSOR company_cur IS
      SELECT name FROM company
       WHERE company_id = :employee.company_id;
BEGIN
   OPEN company_cur;
   FETCH company_cur INTO :employee.company_name;
   IF company_cur%NOTFOUND
   THEN
      MESSAGE
         (' Company with ID ' || TO_CHAR (:employee.company_id)
          ' not found in database.');
   END IF;
   CLOSE company_cur;
END;
This SELECT statement is executed for each record queried into the form. Even if the first twelve employees retrieved in a query all work for the same company, the trigger will open, fetch, and close the company_cur cursor a dozen times -- all to retrieve the same company name again and again. This is not desirable behavior in any application.
Is there a better way? Ideally, you'd want the company name to be already accessible in memory on the client side of the application -- "instantly" available in the form.
One way to achieve this noble objective is to read the contents of the lookup table into a local memory structure on the startup of the form. Unfortunately, this simple solution has a couple of drawbacks:
·         The data, such as the company name, might change during the user session. If you transfer all the names from an RDBMS table to memory and then another user changes the name in the database, your screen data is not updated.
·         You have to set aside sufficient memory to hold all reference values, even if only a few will ever be needed. You will also pay the CPU price necessary to transfer all of this data. This pre-load approach seems like overkill to me.

10.9.5.1 Blending database and PL/SQL table access

A reasonable middle ground between RDBMS-only data and totally local data would store values as they are queried from the database. Then, if that value is needed again in the current session, the application could use the local value instead of issuing another SELECT statement to the database. This would minimize both the memory and the CPU required by the application to return the names for foreign keys.
In this section I offer an implementation of a self-optimizing foreign key lookup process based on PL/SQL tables. I chose PL/SQL tables because I could then store the resulting function in the database and make it accessible to all of my Oracle-based applications, whether they are based on Oracle Forms, PowerBuilder, or Oracle Reports. Let's step through this implementation.

10.9.5.2 Top-down design of blended access

Suppose that I stored company names in a PL/SQL table as they are retrieved from the database and returned to the form. Then, if that same company name is needed a second (or third, or fourth) time, I get it from the memory-resident PL/SQL table rather than from the database. The following example shows the pseudocode which would implement this approach:
  FUNCTION company_name
     (id_in IN company.company_id%TYPE)
  RETURN VARCHAR2
  IS
  BEGIN
     get-data-from-table;
     return-company-name;
  EXCEPTION
     WHEN NO_DATA_FOUND
    THEN
       get-data-from-database-table;
       store-in-PL/SQL-table;
       return-company-name;
END;
This function accepts a single parameter foreign key, id_in (line 2). It first attempts to retrieve the company name from the PL/SQL table (line 6). If that access fails -- if, in other words, I try to reference an undefined row in the PL/SQL table -- then PL/SQL raises the NO_DATA_FOUND exception and control is transferred to the exception section (line 9). The function then gets the name from the database table using a cursor (line 11), stores that information in the PL/SQL table (line 12), and finally returns the value (line 13).
As the user performs queries, additional company names are cached in the PL/SQL table. The next time that same company name is required, the function gets it from the PL/SQL table. In this way, database reads are kept to a minimum and the application automatically optimizes its own data access method. Figure 10.6 illustrates this process.

Figure 10.6: Blended method for lookup of foreign key descriptions

Figure 10.6
Let's see what it takes to actually write this pseudocode in PL/SQL. If I declare my PL/SQL table in a standalone function, then the scope of that PL/SQL table is restricted to the function. As soon as the function terminates, the memory associated with the PL/SQL table is lost. If, on the other hand, I declare the PL/SQL table in a package, that table remains in memory for the duration of my session (see Chapter 16 for more information about packages). I want my PL/SQL table to persist in memory for the duration of a user session, so I will use a stored package structure.

10.9.5.3 The package specification

The specification for my company package is shown in the following example. It contains a single module called name, which returns the name of the company. Does it seem silly to create a package for this single module? Not really. Beyond the technical justification I just provided, you should realize that in a production environment the company package would contain a number of other modules, such as procedures to return data about the company. And if it were the first module for the package, you are planning well for the future by starting with a package instead of a standalone module:
PACKAGE company_pkg
IS
   /*
   || Retrieve the name of the company. If you specify REUSE for
   || the access type, then it will try to get the name from
   || the PL/SQL table. Otherwise, it will just use the database
   || table and do a standard look-up.
   */
   FUNCTION name
      (id_in IN company.company_id%TYPE,
       access_type_in IN VARCHAR2 := 'REUSE')
   RETURN VARCHAR2;
 
END company_pkg;
In this example, the name function takes two parameters: id_in and access_type_in. The access type determines whether the function will try to reuse the name from the PL/SQL table or always get the name from the database table. By providing this parameter, developers can use the company.name function even if they do not want to make use of the PL/SQL table. This would be the case, for example, if the lookup information changes frequently and you cannot afford to use a value returned just minutes past.
What's missing from the package specification is just as interesting as what's there. Notice that the PL/SQL table which holds the names for the foreign key does not appear in the parameter lists of the name module. It isn't even declared in the specification. Instead, the PL/SQL table is declared in the body of the package -- and is completely hidden to developers who make use of this function. By hiding the PL/SQL table, I make this package usable in the Oracle Developer/2000 environment, which, as I've mentioned, is based on PL/SQL Version 1.1 and does not directly support directly PL/SQL tables.

10.9.5.4 The package body

Let's now turn our attention to the package body. In the rest of this section I'll examine the various components of the body individually, listed below:
company_table_type
PL/SQL table type declaration for the table which holds the company names.
company_table
PL/SQL table declaration. The actual declaration of the table.
name_from_database
Private function. Returns the name from the database if not yet stored in the PL/SQL table.
name
Public function. Returns the name to a user of the package. The name might come from the PL/SQL table or the database table.
The body of the company package first declares the table type and table for the company names, as shown below. The datatype for the table's single column is based on the company name column, through use of the %TYPE attribute. This way if the column's length ever changes, this function will automatically adjust:
TYPE company_table_type IS
   TABLE OF company.name%TYPE
   INDEX BY BINARY_INTEGER;
company_table company_table_type;
The private function name_from_database shown in the next example retrieves the company name from the database. The name function calls name_from_database from two different locations. This function is essentially the same code as that shown in the Post-Query trigger at the start of the section:
FUNCTION name_from_database (id_in IN company.company_id%TYPE)
   RETURN company.name%TYPE
IS
   CURSOR comp_cur IS
      SELECT name FROM company
       WHERE company_id = id_in;
   return_value company.name%TYPE := NULL;
BEGIN
   OPEN comp_cur;
   FETCH comp_cur INTO return_value;
   CLOSE comp_cur;
   RETURN return_value;
END;
Notice that I do not check for %FOUND or %NOTFOUND after I have fetched a record from the cursor. If the FETCH fails, then the value of return_value is not changed. The initial value of NULL is returned by the function, which is what I want. Remember that the name_from_database function is only callable from within the package body. Programmers cannot call this function as they would call the company.name function.
Now that you have seen all the private components of the package, let's see how I use them to build the single public module of the company package: the name function (whose full implementation is shown below). The body of the program consists of only six lines:
IF UPPER (access_type_in) = 'REUSE'
THEN
   RETURN company_table (id_in);
ELSE
   RETURN name_from_database;
END IF;
In other words, if reuse is specified then get the value from the PL/SQL table; otherwise get the name from the database. What if the name is not in the PL/SQL table? Then PL/SQL raises the NO_DATA_FOUND exception and the action moves to the exception section, which consists of the following:
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      return_value := name_from_database;
 
      IF return_value IS NOT NULL
      THEN
         company_table (id_in) := return_value;
      END IF;
 
      RETURN return_value;
END;
I try to get the name from the database. If the function returns a non-NULL value, then I save it to the table. Finally, I return that company name. The result is simple, readable code.
In this exception handler you can see the random-access nature of the PL/SQL table. The id_in foreign key value actually serves as the row in the table. I do not have to store the values in the PL/SQL sequentially. Instead, I simply populate those rows of the table which correspond to the foreign keys.

10.9.5.5 The company.name function

The following function combines the various elements described earlier. This name function is defined within the company package:
/* Filename on companion disk: selftune.spp (contains the full code for the package containing this function) */
FUNCTION name
   (id_in IN company.company_id%TYPE,
    access_type_in IN VARCHAR2 := 'REUSE')
   RETURN company.name%TYPE
IS
   return_value company.name%TYPE;
BEGIN
   /* If REUSE (default), then try to get name from PL/SQL table */
   IF UPPER (access_type_in) = 'REUSE'
   THEN
      RETURN company_table (id_in);
   ELSE
      /* Just get the name from the database table. */
      RETURN name_from_database;
   END IF;
 
EXCEPTION
   /* If REUSE and PL/SQL table does not yet have value ... */
   WHEN NO_DATA_FOUND
   THEN
      /* Get the name from the database */
      return_value := name_from_database;
 
      /* If name was found, save to PL/SQL table for next time. */
      IF return_value IS NOT NULL
      THEN
         company_table (id_in) := return_value;
      END IF;
 
      /* Return the company name */
      RETURN return_value;
END;
Using the company package, the Post-Query trigger shown in an earlier example becomes simply:
:employee.company_name := company.name (:employee.company_id);
IF :employee.company_name IS NULL
THEN
   MESSAGE
      (' Company with ID ' || TO_CHAR (:employee.company_id)
       ' not found in database.');
END IF;
In this call to company.name, I did not provide a value for the second parameter, so it takes the default value of REUSE and checks to see if the company name is in the PL/SQL table. Alternatively, I could specify the access type using a GLOBAL variable as follows:
:employee.company_name :=
   company.name (:employee.company_id, :GLOBAL.access_type);
I could then modify the behavior and -- I hope -- the performance of my foreign key lookups without having to change my code. In other words, if I determine that the contents of a particular table are static throughout a session, I could set the GLOBAL to REUSE for that call. If, on the other hand, I determine that the data might change, I could change the GLOBAL to NOREUSE and then force the lookup to go against the database.

10.9.5.6 Performance impact of blended access

How much impact, you might ask, does the company.name function have on the lookup process? I tested the performance of this package with the SQL*Plus script shown in the following example.
The do package in the code below offers a substitution for the DBMS_OUTPUT package. The timer package makes use of DBMS_UTILITY and its GET_TIME function to measure elapsed time in 100ths of seconds. The do and timer packages are defined in Chapter 16.
DECLARE
   c VARCHAR2(100);
BEGIN
   timer.capture;
   FOR i IN 1 .. &num_iterations
   LOOP
      c := company.name (10, '&access_type');
   END LOOP;
   do.pl (c);
   timer.show_elapsed;
END;
/
I executed the script on a 100Mhz 486 workstation with a local Oracle7 for Windows database. The results of this comparison of database and PL/SQL table lookup performance are shown in Table 10.2.


Access Type
Number of Accesses
Elapsed Time (100ths of seconds)
NOREUSE
100
22-28
REUSE
100
0-5
NOREUSE
1000
230-240
REUSE
1000
16-22
An access type of NOREUSE means that each access required a database query. An access type of REUSE means that the first request performed a database query, while all others worked with the PL/SQL table. Clearly, PL/SQL table access offers a significant performance savings -- and this is with a local database. You can expect even more of an improvement with a remote database. The benefit of this approach is obvious: it is always faster to access local memory than the Oracle database shared memory.