Progress
DataServer
for ORACLE Guide
ORACLE Stored Procedures
The DataServer allows you to call ORACLE PL/SQL stored procedures, stored functions, and packages from within Progress 4GL procedures. Stored procedures and stored functions are groups of PL/SQL and control-of-flow statements that access the ORACLE database. Stored procedures and functions can return values or result codes. A package is a group of stored procedures, functions, and related program objects.
The first time you run an ORACLE stored procedure, the ORACLE RDBMS creates an execution plan for it and stores the plan in the database. The next time you run the stored procedure, it runs as a precompiled procedure. This makes accessing the ORACLE database much quicker and more efficient than accessing it with new queries each time.
When you create or update your schema image, the stored procedures, functions, and packages appear in the list of accessible objects along with tables, views, and sequences. Progress allows you to run the stored procedures you create in ORACLE from within Progress procedures. For complete information about how to create and use stored procedures, see the ORACLE PL/SQL User’s Guide and Reference.
The Progress Data Dictionary represents each parameter as a field in the schema image, and you can view its properties by opening its Field Property Sheet.
You run stored procedures from within Progress procedures by using the 4GL RUN STORED-PROC statement. An ORACLE stored procedure that you run with this statement can return two types of values:
Stored procedures called from within Progress applications cannot return Boolean and native ROWID data types. Table 2–11 lists issues that occur when you pass other data types as parameters.
NOTE: You cannot change the data type of a stored procedure parameter. Although you can use the Data Dictionary to view the stored procedure properties in the schema holder, you cannot modify them.The Progress 4GL has statements and functions that allow you to use the return codes and the values of the output parameters. Table 2–12 lists these statements and functions.
The following sections describe how to run ORACLE stored procedures and retrieve return codes, output parameter values, and results sets. All of the following examples of how to run stored procedures in Progress use this stored procedure created in ORACLE:
This PL/SQL code creates the stored procedure pcust and defines three parameters: num, orders, and states. The orders and states parameters are output parameters, which means that the procedure returns values for these parameters to the caller. All the parameters are of the data type INTEGER.
NOTE: Typically, you can have only fifty stored procedures running at one time. This number, however, is further restricted by the number of open cursors you specified for your ORACLE database or for the current session. See the "Index Cursors" section in "Connecting the DataServer," for information on specifying open cursors. Cursor limitations also vary across platforms. See your ORACLE documentation for more information.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |