Progress
DataServer
for ORACLE Guide


Retrieving Results with Cursor Arguments

You can return result rows from stored procedures using named cursors as arguments. Cursor parameters are OUTPUT parameters only.

Use the following syntax to retrieve result rows:

RUN STORED-PROC procedure-name variable = 
  PROC-HANDLE ( parameter-list ). 

For example, the following syntax returns rows from the customer table using the cursor named CUST_CURS:

RUN STORED-PROC open_cust h1 = PROC-HANDLE (CUST_CURS = ?,WITCH_V =1). 

The DataServer retrieves the result rows and places them in a buffer. Specify the ORACLE cursor where you want to fetch and process result rows by using the CURSOR option, as the following syntax and code example show:

FOR EACH buffer-name WHERE PROC-HANDLE = variable
  AND CURSOR = [ [db-name.]procedure-name.]parameter-name :
    DISPLAY buffer-name.
END. 

RUN STORED-PROC open_cust h1 = PROC-HANDLE (CUST_CURS=?,WITCH_V1).
FOR EACH proc-text-buffer WHERE PROC-HANDLE = h1
  AND CURSOR=open_cust.CUST_CURS:
  DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC open_cust. 

This example code runs the stored procedure, open_cust, and displays the results fetched from the CUST_CURS cursor.

NOTE: If multiple cursors are associated with a stored procedure, you must specify a cursor by name when fetching results, otherwise the DataServer returns a run-time error. Always specifying PROC-HANDLE and cursor parameters ensures that your code continues to run if another cursor parameter is added to a stored procedure.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095