Implementing Procedure Result Sets

Brought to you by the Oracle Rdb Data Server Group
Ian Smith  ( ian.smith@oracle.com)

Temporary Tables, Hold Cursors and Stored Procedures

Many applications would like to tailor query results sets for particular users, or to create a crafted set of return data which may be derived from many tables. The aim is to eliminate direct user access to data so that the interface is simplified and the data secured.

Although there is no single feature which provides this functionality, Oracle Rdb7 provides three pieces to build such an interface.

  1. Temporary tables.

  2. Temporary tables provide table storage which is private to the current user session. Temporary tables come in three flavors, but we will restrict this discussion to GLOBAL TEMPORARY tables.
     
  3. Stored procedures.

  4. Stored procedures were introduced in Rdb version 6.0. A stored procedure can contain complex logic and database queries. They can therefore execute SELECT, INSERT, UPDATE and DELETE statements on a temporary table.
     
  5. Declared cursors.

  6. The SQL language has always supported the cursor facility. This allows an application to process rows from a result query, one row at a time. By default a cursor is automatically closed when a COMMIT or ROLLBACK occurs. However, in Oracle Rdb7 HOLD cursors were added which could be defined to remain open across COMMIT and/or ROLLBACK statements.
This technical report describes each feature briefly and then presents a worked example based on the sample PERSONNEL database provided with Oracle Rdb.

Temporary Tables for the Result Data

Oracle Rdb provides three types of tables, BASE tables which hold data, VIEW tables which combine data from several tables, or reduce the data from source tables, and TEMPORARY tables. This later type of table is used to hold transient or temporary results for an application.

A temporary table can be a permanent schema object, however, its data is private to a single database session. When the session is terminated the temporary data is discarded. At the definers option the data can be purged whenever a transaction is committed.

How would such a table be used?

Applications which need to keep intermediate results can do so by writing them to a temporary table instead of opening an operating system flat file, or allocating virtual memory during application execution. In addition these tables act just like other SQL tables, the application can INSERT into them, DELETE data from them, and UPDATE data in them. Most importantly, the application can perform complex queries on these tables, including ORDER BY, GROUP BY and statistical aggregation. Therefore, this type of table is generally much more flexible than a flat file, or an application array.

A temporary table can be created in three ways:

  1. Using the CREATE GLOBAL TEMPORARY TABLE statement. This creates a permanent definition of the table. As each session inserts data into the table it is maintained in virtual memory for use by that session alone.

  2. Using the CREATE LOCAL TEMPORARY TABLE statement. This creates a permanent definition of the table. As each module inserts data into the table it is maintained in virtual memory for use by that module alone.This means that two (or more) stored modules can use the same temporary table but keep their temporary data separate from the other modules.

  3. Using the DECLARE LOCAL TEMPORARY TABLE statement in the CREATE MODULE statement, dynamic SQL, or in interactive SQL. This creates a temporary definition of the table. As each module inserts data into the table it is maintained in virtual memory for use by that module alone.
  4. These temporary tables are created and used on-the-fly in dynamic SQL, and so allow a great deal of flexibility. The names of these tables can be the same as any other base table, view or created temporary table, i.e. there is no interference with existing tables.

    This feature supports true modular programming. For example, if I add an application to the database which uses the temporary table SCRATCH, then I do not want it interfering with the existing production system which maintains results in a temporary table with this name.

For this technical report we will use a created GLOBAL TEMPORARY table for holding the procedure result set. We will also use the same name for the cursor which is used for the temporary table. This allows easier maintenance by adding a visual association between the table and the cursor which uses it.

Stored Procedures to populate the table

A stored procedure will be written which populates the temporary table. A stored procedure can populate many temporary tables if desired, however, for this example we will limit the stored procedure to just one temporary table.

Population of the table will usually involve one or more INSERT statements, possibly using the INSERT ... SELECT syntax to save the query result set. The application is free to also DELETE rows from the table, or UPDATE rows after they have been stored.

The stored procedure may call other stored procedures, or even external procedures which contribute to the data in the temporary table.

Cursor to process the result set

In Oracle Rdb7 a new style of cursor was added which could be directed to remain open across COMMIT and/or ROLLBACK statements. The added flexiblity provided by HOLD cursors can be used with temporary tables created with the ON COMMIT PRESERVE ROWS option.

Worked Example

This worked example is designed to highlight the components which are used for procedure result sets. A production implementation is not limited to the language shown in this example, but would probably involve more of the Oracle Rdb procedural language features.

The aim of this example is provide a manager or supervisor with information on the employees who report to them. This means that the queries which load the temporary table will restrict the rows to those who report to the specified employee, be it a manager, a supervisor or maybe just the employee themself.

The Temporary Table

The stored procedure results will include the the EMPLOYEE_ID, the FIRST_NAME and LAST_NAME of the employee and the amount of their salary. The temporary table definition for SHOW_STAFF is shown below.
                                     
                                       
create global temporary table SHOW_STAFF
  
                                      
   (employee_id        ID_DOM,
  
                                      
    last_name          LAST_NAME_DOM,
  
                                      
    first_name         FIRST_NAME_DOM,
  
                                      
    salary_amount      SALARY_DOM)
  
                                      
   on COMMIT preserve rows;
                                    
                                  
The same column names and domains are used for the temporary tables as were used for the source tables to ensure data type consistency.

The Stored Procedure

The stored procedure for our example determines which type of employee is being referenced. Are they a manager, supervisor or staff member. This information is used to perform different types of queries for each classification. Note that the procedure must clear the results from a previous call so that the data is not combined for several employees.

It is beyond the scope of this article to describe each language feature used by this procedure. If you see a new language feature we encourage you to use the SQL Reference Manual to learn more about it.

                                     
                                       
create module STAFF_SECURE
  
                                      
   language SQL
  
                                      
   authorization PAYROLL_ONLY
                                       
procedure SHOW_STAFF (in :emp_id char(5), out :title varchar(40), out :num_reports integer) comment is 'This procedure lists the reports for the ' / 'various levels in the company. As output ' / 'parameters return the title and the number ' / 'of reports to that manager.'; begin declare :ln, :fn varchar(40); declare :sql_code integer = 0;
! First make sure the employee exists by fetching their name ! select TRIM(last_name), TRIM(first_name) into :ln, :fn from employees where employee_id = :emp_id; get diagnostics exception 1 :sql_code = RETURNED_SQLCODE; if :sql_code <> 0 then signal '00001'; end if;
! First make sure the table is empty ! delete from SHOW_STAFF;
! See if the employee-id is a manager ! if exists (select manager_id from departments where manager_id = :emp_id) then begin set :title = 'Manager: ' || :fn || ' ' || :ln;
! Now fetch all the details of this managers staff ! insert into SHOW_STAFF (employee_id, last_name, first_name, salary_amount) select e.employee_id, e.last_name, e.first_name, s.salary_amount from employees e, job_history j, departments d, salary_history s where d.manager_id = :emp_id and j.department_code = d.department_code and j.job_end is null and s.employee_id = j.employee_id and e.employee_id = s.employee_id and s.salary_end is null; get diagnostics :num_reports = ROW_COUNT; end; elseif exists (select supervisor_id from job_history where supervisor_id = :emp_id) then begin set :title = 'Supervisor: ' || :fn || ' ' || :ln;
! Now fetch all the details of this supervisors staff ! insert into SHOW_STAFF (employee_id, last_name, first_name, salary_amount) select e.employee_id, e.last_name, e.first_name, s.salary_amount from employees e, job_history j, salary_history s where j.supervisor_id = :emp_id and j.job_end is null and s.employee_id = j.employee_id and e.employee_id = s.employee_id and s.salary_end is null; get diagnostics :num_reports = ROW_COUNT; end; else begin set :title = 'Staff Member: ' || :fn || ' ' || :ln;
! Now fetch all the details of this employee ! insert into SHOW_STAFF select e.employee_id, e.last_name e.first_name, s.salary_amount from employees e, salary_history s where e.employee_id = :emp_id and e.employee_id = s.employee_id and s.salary_end is null; set :num_reports = 0; end; end if;
end; -- procedure
end module;

The AUTHORIZATION clause is used for the module so underlying tables can be protected using the GRANT/REVOKE statements. Only the user or rights identifier PERSONNEL_ONLY has access to those tables. Each user can simply be granted EXECUTE access to procedures in the module STAFF_SECURE as shown below. We assume here that REPORTING_STAFF is a role (aka rights identifier) granted to all users who need to run this report.

                                     
                                       
SQL> grant EXECUTE on module STAFF_SECURE to REPORTING_STAFF;
                                    
                                  

Using the cursor

If the procedure result set is processed in more than one transaction then we need to prevent SQL from truncating the table and closing the cursor on COMMIT.
  • Use the ON COMMIT PRESERVE ROWS clause when the temporary table is created. This instructs SQL to retain the temporary data until DISCONNECT time, so it will be the job of the stored procedure to purge the rows for each new query.
  • Use the WITH HOLD clause on the DECLARE CURSOR statement. This instructs the Oracle Rdb server to retain position in the result set so that the next FETCH fetches the next row.

The OPEN of the cursor must be preceded by a call to the stored procedure so that the temporary table can be loaded. The following interactive SQL script shows the SQL statements which are used to declare the cursor, load the result table and fetch the result rows.

                                     
                                       
SQL> declare :title varchar(40);
                                       
SQL> declare :num_reports integer;
                                       
SQL> declare SHOW_STAFF cursor with HOLD
                                       
cont>     for select * from SHOW_STAFF;
                                       
SQL> call SHOW_STAFF ('00205', :title, :num_reports);
  
                                      
TITLE                                      NUM_REPORTS
  
                                      
Manager: Wes Bartlett                                3
                                       
SQL> open SHOW_STAFF;
                                       
SQL> fetch SHOW_STAFF;
  
                                      
EMPLOYEE_ID   LAST_NAME        FIRST_NAME   SALARY_AMOUNT

  
                                      
00205         Bartlett         Wes             $38,223.00
                                       
SQL> commit;
                                       
SQL> fetch SHOW_STAFF;
  
                                      
EMPLOYEE_ID   LAST_NAME        FIRST_NAME   SALARY_AMOUNT
  
                                      
00208         Sciacca          Joe             $41,000.00
                                       
SQL> close SHOW_STAFF;
                                    
                                  

If the application is run from interactive SQL, then a simple SELECT statement will display all the result rows.

                                     
                                       
SQL> declare :title varchar(40);
                                       
SQL> declare :num_reports integer;
                                       
SQL> call SHOW_STAFF ('00164', :title, :num_reports);
  
                                      
TITLE                                      NUM_REPORTS
  
                                      
Manager: Alvin Toliver                               4
                                       
SQL> select * from SHOW_STAFF;
  
                                      
EMPLOYEE_ID   LAST_NAME        FIRST_NAME   SALARY_AMOUNT
  
                                      
00164         Toliver          Alvin           $51,712.00
  
                                      
00167         Kilpatrick       Janet           $17,510.00
  
                                      
00198         Gehr             Leslie          $25,495.00
  
                                      
00217         Siciliano        James           $27,360.00
                                       
4 rows selected
                                    
                                  

Technical Tips: Definers Rights Procedures

There are two ways Oracle Rdb can execute a procedure.
  1. Invoker Rights Modules

  2. It can execute the procedure as though you (the invoker) were executing each query and so validate each table, procedure and function access privileges. This would require you to have access to each referenced database object before the procedure or function would execute.
     
  3. Definers Rights Modules

  4. The alterative is to execute the procedure as though it was the original definer who was executing each query. This means that the invoker only requires access to the procedure itself (via the protections granted to the module).
Definers rights modules are generally preferred over invokers rights procedures simply because they add an extra level of security to the database access.

The AUTHORIZATION identifier can be an OpenVMS rights identifier currently granted to the module creator. The caller of the procedure doesn't need to have this rights identifier, but each table, view and procedure referenced by the procedure must allow access for this AUTHORIZATION identifier.

You should avoid a common mistake made when creating a definers rights module. The creator (JONES) who is granted the rights identifier PERSONNEL_ONLY creates the module without also ensuring that PERSONNEL_ONLY can access the database objects. At runtime the procedure, now running as PERSONNEL_ONLY, fails because it does not have sufficient privileges to access the tables. Make sure that privileges are granted on each database object to PERSONNEL_ONLY.


Top