Problem:
The sample program below illustrates various methods of invoking an Oracle stored procedures and functions. This COBOL program illustrates invoking these procedures and functions with positional values, that is the physical order of the input data in the CALL is associated, in order, with the stored procedure and functions parameters as defined in the CREATE statement. This sample program also demonstrates the use of an Oracle package. An Oracle package is just a logical way of grouping objects together. It consists of a specification which identifies the interface to each object (stored procedure and function) and the body, which implements the procedural logic. The procedures and functions are very simplistic but demonstrate the mechanism for creating and calling ORACLE stored procedures and functions through the ODBC interface in Net Express.
Resolution:
The package GETCOUNT contains a stored procedure 'gcount_sproc' and a function 'gcount_fun'. Both the stored procedure and the function have an input parameter, which is a department number, and return the number of employees in the given department. Example 1 demonstrates using the embedded SQL CALL statement in invoking the 'gcount_sproc' stored procedure, the results of the procedure are passed back through the output host variable param2. Example 2 demonstrates using the embedded SQL CALL statement to invoke the function 'gcount_fun'. The results of the function are set to the host variable param2.
The stored procedure 'mfexecsptest' has two input parameters and one output parameter. It adds the two input parameters together to produce the output value. It is also invoked through an embedded SQL CALL statement. Example 4 demonstrates invoking the procedure 'mfexecsptest'; the results are passed back through the output host variable param3. Example 4 illustrates passing multiple sets of input parameters through arrays to the stored procedure 'mfexecsptest', the output results are likewise returned through a
n array. Example 5 demonstrates passing partial arrays to the stored procedure and the output results are returned through a partial array. Example 6 demonstrates using the embedded SQL CALL statement to invoke the function 'mfexecspfunc'. The function has two input parameters and returns the product of these parameters. The results of the function are set to the host variable param3.
$set sql(dbman=odbc)
program-id. oraclesp.
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC
_______________________EXPANDED SQLCA COPYBOOK________________________________
01 SQLCA.
05 SQLCAID PIC X(8) VALUE "SQLCA ".
05 SQLCABC PIC S9(9) COMP-5 VALUE 136.
05 SQLCODE PIC S9(9) COMP-5 VALUE 0.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-5.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD PIC S9(9) COMP-5 OCCURS 6 VALUE 0.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARN10 PIC X.
10 SQLWARNA REDEFINES SQLWARN10 pic x.
05 SQLSTATE PIC X(5).
________________________END OF COPYBOOK______________________________________________
EXEC SQL INCLUDE DEPT END-EXEC
________________________EXPANDED DEPT COPYBOOK_____________________________________
EXEC SQL DECLARE DEPT TABLE
( DEPTNO DECIMAL(2, 0) NOT NULL
,DNAME VARCHAR2(14)
,LOC VARCHAR2(13)
) END-EXEC.
***
