RDBMS stored procedure basics

In the OpenEdge environment, you can think of a stored procedure definition as having two basic, interrelated parts:

Execution controls to run and close a store procedure — Comprise the information needed to execute a stored procedure request against the Oracle data source. At a minimum, all stored procedures discussed in this guide are assessable using the RUN STORED-PROCEDURE statement.

Language elements that enable access to specific data results — Qualify the retrieved data, or result set, that the stored procedure's execution returns. Various keywords, phrases, statements, and syntax elements support different retrieval options for stored procedure output. This part of the stored procedure implementation reflects your analysis of your data needs; based on this analysis, you determine the additional syntax elements you need to define the output elements and data results you want retrieved.

The following table identifies and briefly introduces the elements that comprise a stored procedure definition. These elements are discussed later in this chapter.

Table 19. Stored procedure language elements
ABL	Description
RUN STORED-PROCEDURE statement	Executes a stored procedure
PROC-HANDLE phrase	Allows you to specify a handle to identify a stored procedure
PROC-STATUS phrase	Reads the return value
LOAD-RESULT-INTO phrase	Allows data from a result set that is returned for a foreign data source to be put into one or more temp-tables
PARAM phrase	Identifies run-time parameters to be passed to and/or from the stored procedure
CLOSE STORED-PROCEDURE statement	Enables the values to be retrieved from the output parameters that you defined for the stored procedure, finalizes result sets data processing, and tells OpenEdge that the stored procedure has ended
NO-ERROR phrase	Allows native database errors (that occur during stored procedure execution) to be evaluated and handled by the application. Note: This can also be achieved by a CATCH end block in the stored procedure. For more information on handling errors using the NO-ERROR option or a CATCH end block, see Error handling.

Note: You can substitute the abbreviations RUN STORED-PROC and CLOSE STORED-PROC for the full names RUN STORED-PROCEDURE and CLOSE STORED-PROCEDURE, respectively. The remainder of this guide generally uses the abbreviated form.

See RUNSTORED-PROCEDURE details for more details about the reference entries presented in the table above.

As noted in the table above, you can pass data types in the RUN STORED-PROCEDURE statement using the PARAM phrase. The following table lists issues that occur when you pass certain data types as parameters.

Table 20. Argument data types for stored procedures
OpenEdge	Oracle data source
DECIMAL INTEGER INT64	The DataServer converts each if these data types in the schema image to the equivalent OpenEdge data type as follows: DECIMAL = NUMBER with precision and scale INTEGER = CURSOR INT64 = NUMBER with precision 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 Data Dictionary to update the data type and format information in the Field Property Sheet for the parameter.
VARCHAR2	In Oracle, VARCHAR2 parameters cannot be greater than 4000 characters. If the VARCHAR2 parameter exceeds this limit, it causes an error. Note: If you use a parameter that is larger than 255 characters, you need to change the _for-maxsize value for this parameter.
DATE DATETIME DATETIME-TZ	You can specify a DATE, DATETIME or DATETIME-TZ data type as a parameter when using RUN STORED-PROCEDURE. You can also specify a DATETIME or DATETIME-TZ data type in a temp table used to receive results from a stored procedure using the LOAD-RESULTS-INTO phrase.
MEMPTR	You can specify a MEMPTR data type in a Paramphrase as an INPUT or an OUTPUT parameter to receive results from a corresponding BLOB data type parameter to or from a RUN STORED-PROCEDURE statement.
LONGCHAR	You can specify a LONGCHAR data type in a Paramphrase as an INPUT or an OUTPUT parameter to receive results from a corresponding CLOB data type parameter to or from a RUN STORED-PROCEDURE statement.

Note these stored procedure points:

Input and output parameters are displayed as fields.

If you are running several stored procedures, run them serially and process all the results from one stored procedure and close the procedure before you run a second one. By default, the DataServer allows one active request for running a stored procedure. It is not necessary to specify the PROC-HANDLE phrase when procedures are run serially.

Stored procedures implemented through an Oracle DataServer already implicitly supports a large value (that is, greater than 32 bits) because the Oracle NUMBER data type supports 64-bit binary values.

When you run stored procedures concurrently, the DataServer uses one connection to the data source per procedure. If different stored procedures attempt to update the same record from a single client's requests, the connections could block each other or a deadlock might occur.

You must define a PROC-HANDLE phrase for each stored procedure phrase that is simultaneously active. This technique provides a CLOSE STORED-PROC statement that can identify the targeted open procedure and close it.

In contrast, since a stored procedure executed with the LOAD-RESULT-INTO phrase implicitly closes the procedure once the execution ends and the data retrieved is placed into temp tables, it essentially runs serially and has no use for a PROC-HANDLE.

When you create or update your schema image, the stored procedures appear in the list of accessible objects along with tables, view, and sequences. OpenEdge allows you to run the stored procedures that you create in data sources using the procedure definitions in your schema image. See your Oracle documentation for complete information about creating and using stored procedures.

If ABL that executes a stored procedure is already within a transaction block, the stored procedure becomes an extension of that transaction and will not commit to the database until the ABL transaction is completed. However, because the stored procedure does not execute as part of an ABL client process, it cannot be rolled back by ABL.

The DataServer cannot roll back sub-transactions in the stored-procedure context since it has no control over what the stored procedure executes.

If you pass a DATE data type as an input parameter and use it in an equality test, the test might fail. In this case, use the trunc function in the stored procedure to isolate parts of the date structure for which you might want to test. 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;

For backward compatibility, Oracle DATE columns are pulled as OpenEdge DATE and OpenEdge INTEGER into the schema holder by default. If you create a temp-table using the LIKE phrase with a table in the schema holder that has mapped the Oracle DATE to OpenEdge DATE and INTEGER fields, the temp table definition will include both columns. If you use the LOAD-RESULTS_INTO phrase to receive the results set from a SEND-SQL-STATEMENT stored procedure or a non-ABL stored procedure, then the PL/SQL statement in the stored procedure that produces the resultant records should be constructed in such a way as to select the Oracle DATE into the two separate columns of the date and time portion that will match the temp table columns. Not mapping the temp table properly will result in a "Number of fields" mismatch error. Oracle functions TRUNC and EXTRACT can be used to extract the date and time portions from the Oracle Date into separate columns for the results buffer.

The following example loads the results of an Oracle DataServer schema definition created with the LIKE phrase into a temp-table using SEND-SQL-STATEMENT. Oracle table mydate with two columns, dt_num and dt_date is utilized in the schema holder to create a temp-table where stored-procedure results are loaded. The SQL is modified to produce the expected resultant.

DEFINE VARIABLE tth AS HANDLE NO-UNDO.
DEFINE TEMP-TABLE tt NO-UNDO LIKE mydate.
DEF VAR timeinsec AS CHAR NO-UNDO.

timeinsec = "EXTRACT(hour from to_timestamp(to_char(dt_date),
'yyyymmddhh24miss'))*3600 + EXTRACT (minute from
to_timestamp(to_char(dt_date), 'yyyymmddhh24miss'))*60".

DEF VAR sql-string AS CHAR NO-UNDO.

sql-string = "select dt_num, TRUNC(dt_date)," + timeinsec + " from
mydate".
tth = TEMP-TABLE tt:HANDLE.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tth
NO-ERROR(sql-string).
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = tth.

FOR EACH tt.
DISP tt.
END.

The RDBMSStored Procedure Details presents more details about the use of the RUN STORED-PROC statement.