Progress
DataServer
for ORACLE Guide


RUN STORED-PROCEDURE Statement

Runs an ORACLE stored procedure:

SYNTAX

RUN STORED-PROCEDURE procedure [ integer = PROC-HANDLE ]
[ ( [ output ] parameter , [ output ] parameter ) ] 
[ NO-ERROR ]  

procedure

The name of the stored procedure that you want to run or the built-in procedure name, send-sql-statement, which passes PL/SQL statements to ORACLE.

integer = PROC-HANDLE

An integer whose value uniquely identifies the stored procedure that produces the results returning from the ORACLE database.

parameter

A run-time parameter to be passed to the stored procedure. A parameter has the following syntax:

SYNTAX
{ [ INPUT | OUTPUT | INPUT-OUTPUT ]
  [ PARAM parameter-name = ] expression } 

INPUT is the default. In ORACLE, OUTPUT and INPUT-OUTPUT work the same way. If you do not use the parameter name, you must supply all of the parameters in correct order. If you do use the parameter name, you must precede your assignment statement with the keyword PARAM. You must also name parameters to pass values that are different from the default values. If you do not supply a parameter, and no default is specified in the stored procedure, you receive a run-time error.

You can designate a parameter as an extent in the Progress Data Dictionary. You can also use a named ORACLE cursor as an OUTPUT parameter. If a stored procedure has multiple cursors, you must specify a cursor by name when fetching results.

NO-ERROR

Specifies that any ERROR conditions that the RUN STORED-PROC statement produces are suppressed. Before you close a stored procedure, check the ERROR-STATUS system handle for information about any errors that occurred. You receive an error when you attempt to close a stored procedure that did not start.

EXAMPLES

This procedure runs the ORACLE stored procedure pcust and writes the results of the stored procedure into the Progress-supplied buffer, proc-text-buffer. The same code works for accessing a stored procedure from an ODBC-compliant data source:

DEFINE VAR handle AS INTEGER.

RUN STORED-PROCEDURE pcust handle = PROC-HANDLE 
(10, OUTPUT 0, OUTPUT 0) NO-ERROR.
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle:
  DISPLAY proc-text-buffer.
END.
IF ERROR-STATUS:ERROR
THEN DO:
  MESSAGE "Stored Procedure failed to run".
END.
ELSE CLOSE STORED-PROCEDURE pcust WHERE PROC-HANDLE = handle. 

This procedure uses the send-sql-statement option of the RUN STORED-PROCEDURE statement to send SQL to ORACLE. It writes the results of the stored procedure into the Progress-supplied buffer, proc-text-buffer. The same code works for sending SQL to an ODBC-compliant data source:

DEFINE VAR handle1 AS INTEGER.

RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
     ("SELECT name, cust_num FROM customer").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
   display proc-text.
   END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.
END. 

This code example shows how to trap errors from the non-Progress DBMS within a procedure:

DEFINE VAR h1 AS INTEGER.
DEFINE VAR j AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
  ("select count (*) from xxx.customer where name between ’A’ and ’Z’ ").

IF ERROR STATUS:ERROR THEN DO:
  DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
    MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
        ERROR-STATUS:GET-MESSAGE(j).
  END.
END.

CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1. 

NOTE

SEE ALSO

CLOSE STORED-PROCEDURE Statement, PROC-HANDLE Function, PROC-STATUS Function


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