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.

Table 2–11: Argument Data Types 
Data Type
Behavior
DECIMAL
FLOAT
INTEGER
The DataServer represents all three data types as the Progress INTEGER data type in the schema image. To preserve the scale and precision of these data types, you must manually update the information in the schema image for these parameters. Use the Progress Data Dictionary to update the data type and format information in the Field Property Sheet for the parameter.
VARCHAR2
In ORACLE7, VARCHAR2 parameters cannot be greater than 255. If they exceed this limit, they cause an ORACLE error. In ORACLE8, the limit is 2000 characters.
DATE
If you are passing a DATE data type as an input parameter and using it in an equality test, the equality test might fail. In this case, use the trunc function in the stored procedure. For example:

procedure x_date (indate in date, outdate out date)
as begin
select date_terminate into outdate from datetbl
     where trunc(hire_date) = trunc (indate);
end;

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.

Table 2–12: Returning Values from Stored Procedures 
Progress 4GL
Description
CLOSE STORED-PROC statement
Retrieves the values from the output parameters you defined for the stored procedure and tells Progress that the stored procedure has ended.
PROC-HANDLE function
Allows you to specify a handle to identify a stored procedure.
PROC-STATUS function
Reads the return value.

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:

CREATE PROCEDURE pcust (num in int, orders out int, states out int) 
AS BEGIN 
If num IS NULL THEN
    raise_application_error (-20101, ’Cust Num is missing’);
ELSE
  SELECT COUNT (*) INTO orders FROM customer, order_
    WHERE customer.Cust_num = order_.Cust_num AND customer.Cust_num > num;
SELECT count(*) into states from customer WHERE cust_num > num;
END IF;
END; 

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