Problem:
How to call ORACLE stored procedures and functions using the ODBC interface in Net Express .
Resolution:
The attached program 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.
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 an 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.
******************************************************************
* COBOL DECLARATION FOR TABLE DEPT *
******************************************************************
01 DCLDEPT.
03 DEPT-DEPTNO PIC S9(02) COMP-3.
03 DEPT-DNAME PIC X(14).
03 DEPT-LOC PIC X(13).
******************************************************************
* COBOL INDICATOR VARIABLES FOR TABLE *
******************************************************************
01 DCLDEPT-NULL.
03 DEPT-DNAME-NULL PIC S9(04) COMP-5.
03 DEPT-LOC-NULL PIC S9(04) COMP-5.
__________________________ END OF COPYBOOK__________________________________________
01 mfsqlmessagetext pic x(255).
01 param1 pic s9(9) comp-5.
01 param2 pic s9(9) comp-5.
01 param3 pic s9(9) comp-5.
78 aSize value 5.
01 arrayparam1 pic s9(9) comp-5 occurs aSize.
01 arrayparam2 pic s9(9) comp-5 occurs aSize.
01 arrayparam3 pic s9(9) comp-5 occurs aSize.
01 arrayCount pic s9(9) comp-5.
01 i pic s9(9) comp-5.
procedure division.
exec sql whenever sqlerror goto sql-error end-exec
EXEC SQL
connect to 'ora8_odbc' user 'scott.tiger'
END-EXEC
****************Creating Stored Procedure MFEXECSPTEST****************************
exec sql
create or replace procedure mfexecsptest
(param1 in integer,
param2 in integer,
param3 out integer) is
begin
param3 := param1 param2;
end mfexecsptest;
end-exec
****************Creating Package specification GETCOUNT*****************************
****************For stored procedure gcount_proc and function gcount_fun*****************
exec sql
create or replace package getcount as
PROCEDURE gcount_proc(indept IN NUMBER,
dept_tot OUT INTEGER);
FUNCTION gcount_fun(indept NUMBER) RETURN NUMBER;
end getcount;
end-exec
*********Creating Pakage Body GETCOUNT for gcount_proc and gcount_fun*******************
exec sql
create or replace package body getcount as
PROCEDURE gcount_proc (indept IN NUMBER,
dept_tot OUT INTEGER) is
dept_count INTEGER;
BEGIN
select count(*)
into dept_count
from EMP
where DEPTNO = indept;
IF dept_count IS NULL THEN
dept_tot := 0;
ELSE
dept_tot := dept_count;
END IF;
END gcount_proc;
FUNCTION gcount_fun (indept NUMBER)
RETURN NUMBER is
dept_count INTEGER;
BEGIN
select count(*)
into dept_count
from EMP
where DEPTNO = indept;
IF dept_count IS NULL THEN
RETURN 0;
ELSE
RETURN dept_count;
END IF;
END gcount_fun;
END getcount;
end-exec
****EXAMPLE 1*****Calling stored procedure gcount_proc in package getcount**********
move 20 to param1
move 0 to param2
exec sql
call getcount.gcount_proc(:param1 in,:param2 out)
end-exec
display "Testing package procedure"
display "There are " param2 " employees in department" param1
****EXAMPLE 2*******Calling function gcount_fun in package getcount****************
move 10 to param1
move 0 to param2
exec sql
:param2 = call getcount.gcount_fun(:param1 in)
end-exec
display "Testing package function"
display "There are " param2 " employees in department" param1
****EXAMPLE 3*******Calling mfexecsptest using positional parameters***************
move 2 to param1
move 2 to param2
move 0 to param3
exec sql
call mfexecsptest( :param1, :param2, :param3 output)
end-exec
display "Testing positional parameters"
display param1 " " param2 " = " param3
****EXAMPLE 4*******Calling mfexecsptest using whole arrays****************
perform varying i from 1 by 1 until i > aSize
move i to arrayParam1(i)
add i i giving arrayParam2(i)
move 99 to arrayParam3(i)
end-perform
exec sql
call mfexecsptest(
:arrayParam1, :arrayParam2, :arrayParam3 output)
end-exec
display "Testing whole array call"
perform varying i from 1 by 1 until i > aSize
display arrayParam1(i) " " arrayParam2(i)
" = " arrayParam3(i)
end-perform
****EXAMPLE 5*******Calling mfexecsptest using partial arrays****************
perform varying i from 1 by 1 until i > aSize
move i to arrayParam1(i)
add i i giving arrayParam2(i)
move 99 to arrayParam3(i)
end-perform
move 3 to arrayCount
exec sql
for :arrayCount call mfexecsptest(
:arrayParam1, :arrayParam2, :arrayParam3 output)
end-exec
display "Testing partial array call(1st 3 rows)"
perform varying i from 1 by 1 until i > aSize
display arrayParam1(i) " " arrayParam2(i)
" = " arrayParam3(i)
end-perform
****************Creating Function MFEXECSPFUNC****************************
EXEC SQL
create or replace function mfexecspfunc
(param1 in integer,
param2 in integer)
return integer is
begin
return param1 * param2;
end mfexecspfunc;
END-EXEC
****EXAMPLE 6*******Calling function mfexecpfunc**************************************
move 5 to param1
move 7 to param2
move 0 to param3
exec sql
:param3 = call mfexecspfunc(:param1, :param2)
end-exec
display "Testing function result"
display param1 " * " param2 " = " param3
display "Testing result sets"
EXEC SQL
DISCONNECT CURRENT
END-EXEC
stop run.
sql-error section.
display "error " mfsqlmessagetext
stop run.
sql-warn section.
display "warning " mfsqlmessagetext
stop run.
