Oracle was slow to introduce support for returning result sets from stored procedures. When they finally did add such support, they did so in a manner that was somewhat different than the approach used by other vendors.

This has a couple of consequences for PowerBuilder developers. The first is that the PBDBMS approach was developed within PowerBuilder as an interim workaround. The second is that using stored procedures to return result sets is not as widespread a practice with Oracle databases as it is with other database products.

This article (an excerpt from an upcoming book on PowerBuilder 9) is an attempt to address this situation. The interim PBDBMS solution is going away. In PowerBuilder 8, support for the PBDBMS was restricted to the O73 driver and with PowerBuilder 9, support for it will be dropped completely. If you're still using that old technique and need to migrate, or you would just like to learn the new one, this article should help.

Using a Stored Procedure for the SQL Source for a DataWindow
While a SQL SELECT statement is the typical way data is retrieved from the database into a DataWindow, there is another approach. Using a stored procedure to do the retrieve is popular with a number of other databases because they offer some advantages over a standard SELECT statement. First, stored procedures in the database are precompiled, which means they'll significantly outperform uncompiled SQL statements. In addition, stored procedures provide for data-hiding, i.e., the end user doesn't need to know or have the ability to access the underlying tables in order to retrieve the data.

Note that neither of these advantages are significant when using an Oracle database. Provided the application is being run with binding enabled, the database is storing the bound and compiled statements in its SHARED_SQL area, which means that SQL SELECT statements will achieve similar performance as stored procedures. Further, Oracle's role-based security model can and should be used to restrict the user's ability to access data only in the context of the application session. That is, the role they need to access the data can be enabled only for the application session, and only for the duration of the application session (nondefault roles expire when the session ends).

Regardless, using stored procedures for the data source is an option, and may particularly be preferred for consistency when the application has to support a number of different database vendors, and stored procedures are used as the source for other vendors.

Ref Cursors
Oracle introduced the concept of ref cursors (a cursor that can be passed back to a client) with version 7.2. Prior to that, Sybase provided a workaround in PowerBuilder known as PBDBMS (named after the package created by PowerBuilder in the database used to return the data) to enable you to return a result set from an Oracle stored procedure. Support for that feature is being dropped and its use should be discontinued. Therefore our discussion will focus entirely on the ref cursor approach.

To return a ref cursor, it must first be declared as a data type, generally in a package specification. A ref cursor can be constrained, which means that the result set description is defined when the ref cursor is declared, or it may be unconstrained, which means that the result set description is determined upon use. PowerBuilder supports the use of either, but it does bind the DataWindow to the particular result set description it obtained when the DataWindow was created through the execution of the stored procedure. Therefore, while both are supported, the use of an unconstrained ref cursor could lead to runtime errors. This is particularly true because the developer could inadvertently modify the result set description after the initial creation of the DataWindow, and the unconstrained ref cursor would allow the modified statement to compile. In the case of a constrained ref cursor, a modification to the result set without modification of the ref cursor data type declaration would result in an error during the stored procedure compile, alerting the developer to the issue. On the other hand, there are some techniques - particularly using object types to store and return the data - that necessitate the use of an unconstrained ref cursor.

The ref cursor can be populated by a number of methods, and a straight select statement is the most common. However, there are also methods available to handle intermediate data. With some other databases, this is accomplished through the use of temporary tables. Much the same can be done with Oracle, except that instead of creating a physical temporary table, we create something similar in memory. We'll use the code in Listing 1 for our examples.

Note that PL/SQL record types can also be declared to be equivalent to a table definition through the %ROWTYPE attribute. So we could have used this instead:

TYPE r_employee IS emp%rowtype ;

Or more simply:

TYPE constrainedcursor IS REF CURSOR RETURN emp%rowtype ;

The actual element-by-element record type declaration is used in the examples in Listing 2 because it offers greater flexibility with regard to mixing elements from more than one table (or elements that are independent of any table) though it does require a bit more work.

Before we show the unconstrained example, we'll create a couple of object types first.

CREATE OR REPLACE TYPE EMPINFO IS OBJECT (
empno number(4,0),
ename varchar2(10),
job varchar2(9)
) ;

CREATE OR REPLACE TYPE EMPINFOLIST AS TABLE OF EMPINFO ;

We're going to use those object types as a method of storing intermediate data. Note that we used actual data types rather than column type references to create the object type (see Listing 3). (Listings 3-5 can be downloaded from http://gemsres.com/photos/story/res/42529/source.html.)

The "TABLE" operator was introduced with Oracle8i as a replacement for the "THE" operator in Oracle 8.0. If you're working with an Oracle 8 database, the stored procedure would be modified as follows:

-- Finally, let's pass back the data
OPEN c_empinfo FOR
SELECT *
FROM THE ( SELECT ( CAST ( v_empinfolist AS empinfolist )
) FROM DUAL ) ;

Either approach requires object support to be enabled on the server. In Oracle 8 you must have the enterprise edition in order to use objects. 8i and later support objects in the standard edition. To verify whether you have support for objects, use the following query, which will return TRUE or FALSE to indicate if the option is installed:

select value from v$option where parameter = 'Objects'

If object support is not enabled on the server or if you're working with a 7.2 or 7.3 database (which does not have the objects option), we can use something more like a physical temp table approach. We'll need to create a table that will define our result set, just like the object type would:

CREATE TABLE TEMP_EMPINFO (
empno number(4,0),
ename varchar2(10),
job varchar2(9) ) ;

We'll modify our stored procedure as shown in Listing 4.

Although our temp table in this case is permanent, we don't need to track the session ID in it because we aren't performing a COMMIT without our procedure. The only data we'll see (and delete) in the table is the data we've put there within the session.

Beginning with Oracle8i, support for temporary tables was introduced with the "GLOBAL TEMPORARY" keyword pair for the CREATE TABLE DDL statement. It basically does the same thing we've accomplished here.

Using Stored Procedures for DataWindow Updates
If you're going to be using stored procedures to query the database, either you won't allow updates from the DataWindow (it's a report) or you'll allow updates and there's a good chance you'll be using stored procedures for the updates as well. If you're using the stored procedure to provide for information hiding (preventing the user from selecting directly off the base tables), it doesn't make sense to allow them to have direct update capabilities on those same tables.

We'll add a couple more procedures to our package (see Listing 5), and use them for the update specs (see Figure 1).

Conclusion
Hopefully, this article will have removed some of the mystery concerning how to use Oracle stored procedures to return result sets. While the server-side technique differs considerably from that used by other vendors and the interim PBDMS approach, I believe you'll find it flexible and powerful once you get comfortable using it.

--This article was originally published on PBDJ.