Progress
DataServer
for ORACLE Guide


Running a Stored Procedure

The Progress 4GL statement RUN STORED-PROCEDURE allows you to run an ORACLE stored procedure. You must indicate the end of a stored procedure in your Progress procedure by using the CLOSE STORED-PROCEDURE statement.

This is the partial syntax for the RUN STORED-PROCEDURE statement:

RUN STORED-PROC procedure  [ integer-variable = PROC-HANDLE ]
  [ ( [ output ] parameter , [ output ] parameter ) ] 

This is the partial syntax for the CLOSE STORED-PROCEDURE statement:

CLOSE STORED-PROC procedure 

For example, the following Progress 4GL code runs the ORACLE stored procedure pcust:

DEFINE VAR h1 AS int.
RUN STORED-PROC pcust h1 = PROC-HANDLE (20, OUTPUT 0, OUTPUT 0).
CLOSE STORED-PROC pcust WHERE PROC-HANDLE = h1.
 DISPLAY pcust.orders pcust.states. 

This code defines an integer variable that serves as a handle for identifying the stored procedure. If you have only one active stored procedure, you do not have to specify a handle. However, it is good programming practice to use handles to identify all your stored procedures.

The pcust stored procedure passes the values 20, 0, and 0 to the three parameters (specifying orders and states as output parameters) and displays the results. The Progress procedure uses the CLOSE STORED-PROC statement to fetch the orders and states output parameters, then displays them. The stored procedure does not return the value of the output parameters unless you request them with the keyword OUTPUT or INPUT-OUTPUT when you execute the procedure.

You can close all stored procedures at once with the following statement:

RUN STORED-PROC closeallprocs. 

See "Stored Procedure Reference," for a description of the complete syntax for the Progress statements and functions that support running ORACLE stored procedures.


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