Implementing Procedure Result Sets
Brought to you by the Oracle Rdb Data Server Group
Temporary Tables, Hold Cursors and Stored ProceduresMany 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.
Temporary Tables for the Result DataOracle 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:
Stored Procedures to populate the tableA 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 setIn 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 ExampleThis 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 TableThe 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.
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 ProcedureThe 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.
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.
Using the cursorIf 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.
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.
If the application is run from interactive SQL, then a simple SELECT statement will display all the result rows.
Technical Tips: Definers Rights ProceduresThere are two ways Oracle Rdb can execute a procedure.
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.