Using Oracle stored procedures as a data source

This section describes how you can use Oracle stored procedures.

What is an Oracle stored procedure?

Oracle defines a stored procedure (or function) as a named PL/SQL program unit that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task.

Stored procedures can take parameters and return one or more result sets (also called cursor variables). You create stored procedures in your schema and store them in the data dictionary for use by multiple users.

What you can do with Oracle stored procedures

Ways to use Oracle stored procedures

You can use an Oracle stored procedure in the following ways in your PowerBuilder application:

  • As a data source for DataWindow objects

  • Called by an embedded SQL DECLARE PROCEDURE statement in a PowerBuilder application (includes support for fetching against stored procedures with result sets)

  • Called as an external function or subroutine in a PowerBuilder application by using the RPCFUNC keyword when you declare the procedure

    For information about the syntax for using the DECLARE PROCEDURE statement with the RPCFUNC keyword, see the PowerScript Reference.

Procedures with a single result set

You can use stored procedures that return a single result set in DataWindow objects and embedded SQL, but not when using the RPCFUNC keyword to declare the stored procedure as an external function or subroutine.

Procedures with multiple result sets

You can use procedures that return multiple result sets only in embedded SQL. Multiple result sets are not supported in DataWindows, reports, or with the RPCFUNC keyword.

Using Oracle stored procedures with result sets

Overview of basic steps

The following procedure assumes you are creating the stored procedure in the ISQL view of the Database painter in PowerBuilder.

To use an Oracle stored procedure with a result set:

  1. Set up the ISQL view of the Database painter to create the stored procedure.

  2. Create the stored procedure with a result set as an IN OUT (reference) parameter.

  3. Create DataWindow objects that use the stored procedure as a data source.

Setting up the Database painter

When you create a stored procedure in the ISQL view of the Database painter, you must change the default SQL statement terminator character to one that you do not plan to use in your stored procedure syntax.

The default SQL terminator character for the Database painter is a semicolon (;). If you plan to use a semicolon in your Oracle stored procedure syntax, you must change the painter's terminator character to something other than a semicolon to avoid conflicts. A good choice is the backquote ( ` ) character.

To change the default SQL terminator character in the Database painter:

  1. Connect to your Oracle database in PowerBuilder as the System user.

    For instructions, see Defining the Oracle database interface.

  2. Open the Database painter.

  3. Select Design>Options from the menu bar.

    The Database Preferences dialog box displays. If necessary, click the General tab to display the General property page.

  4. Type the character you want (for example, a backquote) in the SQL Terminator Character box.

  5. Click Apply or OK.

    The SQL Terminator Character setting is applied to the current connection and all future connections (until you change it).

Creating the stored procedure

After setting up the Database painter, you can create an Oracle stored procedure that has a result set as an IN OUT (reference) parameter. PowerBuilder retrieves the result set to populate a DataWindow object.

There are many ways to create stored procedures with result sets. The following procedure describes one possible method that you can use.

For information about when you can use stored procedures with single and multiple result sets, see What you can do with Oracle stored procedures.

To create Oracle stored procedures with result sets:

  1. Make sure your Oracle user account has the necessary database access and privileges to access Oracle objects (such as tables and procedures).

    Without the appropriate access and privileges, you will be unable to create Oracle stored procedures.

  2. Assume the following table named tt exists in your Oracle database:

    a

    b

    c

    1

    Newman

    sysdate

    2

    Everett

    sysdate


  3. Create an Oracle package that holds the result set type and stored procedure. The result type must match your table definition.

    For example, the following statement creates an Oracle package named spm that holds a result set type named rctl and a stored procedure named proc1. The tt%ROWTYPE attribute defines rctl to contain all of the columns in table tt. The procedure proc1 takes one parameter, a cursor variable named rc1 that is an IN OUT parameter of type rctl.

    CREATE OR REPLACE PACKAGE spm
       IS TYPE rctl IS REF CURSOR
       RETURN tt%ROWTYPE;
       PROCEDURE proc1(rc1 IN OUT rctl);END;`
  4. Create the Oracle stored procedure separately from the package you defined.

    The following examples show how to create two stored procedures: spm_proc 1 (returns a single result set) and spm_proc2 (returns multiple result sets).

    The IN OUT specification means that PowerBuilder passes the cursor variable (rc1 or rc2) by reference to the Oracle procedure and expects the procedure to open the cursor. After the procedure call, PowerBuilder fetches the result set from the cursor and then closes the cursor.

    spm_proc1 example for DataWindow objects

    The following statements create spm_proc1 which returns one result set. You can use this procedure as the data source for a DataWindow object in PowerBuilder.

    CREATE OR REPLACE PROCEDURE spm_proc1(rc1 IN OUT spm.rctl)
    AS
    BEGIN
       OPEN rc1 FOR SELECT * FROM tt;
    END;`

    spm_proc2 example for embedded SQL

    The following statements create spm_proc2 which returns two result sets. You can use this procedure only in embedded SQL.

    CREATE OR REPLACE PROCEDURE spm_proc2 (rc1 IN OUT spm.rctl, rc2 IN OUT spm.rctl)
    AS
    BEGIN
       OPEN rc1 FOR SELECT * FROM tt ORDER BY 1;
       OPEN rc2 FOR SELECT * FROM tt ORDER BY 2;END;`

Error checking

If necessary, check the Oracle system table public.user_errors for a list of errors.

Creating the DataWindow object

After you create the stored procedure, you can define the DataWindow objects that uses the stored procedure as a data source.

You can use Oracle stored procedures that return a single result set in a DataWindow object. If your stored procedure returns multiple result sets, you must use embedded SQL commands to access it.

The following procedure assumes that your Oracle stored procedure returns only a single result set.

To create a DataWindow object using an Oracle stored procedure with a result set:

  1. Select a presentation style on the DataWindow page of the New dialog box and click OK.

  2. Select the Stored Procedure icon and click OK.

    The Select Stored Procedure wizard page displays, listing the stored procedures available in your database.

  3. Select the stored procedure you want to use as a data source, and click Next.

  4. Complete the wizard to define the DataWindow object.

    When you preview the DataWindow object or call Retrieve, PowerBuilder fetches the result set from the cursor in order to populate the DataWindow object. If you selected Retrieve on Preview on the Choose Data Source page in the wizard, the result set displays in the Preview view when the DataWindow opens.

Using a large-object output parameter

You can define a large object (LOB) as an output parameter for an Oracle stored procedure or function to retrieve large-object data. There is no limit on the number of LOB output arguments that can be defined for each stored procedure or function.

In Oracle 10g, the maximum size of LOB datatypes has been increased from 4 gigabytes minus 1 to 4 gigabytes minus 1 multiplied by the block size of the database. For a database with a block size of 32K, the maximum size is 128 terabytes.

RPC calls to stored procedures with array parameters

If your application performs a remote procedure call (RPC) that passes an array parameter to an Oracle stored procedure, the array size in the stored procedure must not be zero. If the array size is uninitialized (has no size), the PBVM returns an error.