Using Oracle stored procedures with result sets – PB Docs 2017

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.

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).

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.

   PowerBuilder

   CREATE OR REPLACE PACKAGE spm IS TYPE rctl IS REF CURSOR RETURN tt%ROWTYPE; PROCEDURE proc1(rc1 IN OUT rctl);END;`

   1 2 3 4

   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.

   PowerBuilder

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

   1 2 3 4 5

   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.

   PowerBuilder

   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;`

   1 2 3 4 5

   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.

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.