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.

See RUN STORED-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.

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.
  Note:

  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 RDBMS Stored Procedure Details presents more details about the use of the RUN STORED-PROC statement.