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:

Syntax

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

For example, the following code example 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:

Syntax

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.

The previous 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.