Created On:  29 June 2011

Problem:

When using Pro*COBOL with Oracle in Net Express it was possible to pass COBOL arrays as parameters to a stored procedure by using the EXEC SQL EXECUTE ...END-SQL construct. Pro*COBOL is not supported in a managed code environment so how can I still pass arrays as parameters to an Oracle stored procedure when using ADO.NET?

Resolution:

This is currently not supported directly using embedded EXEC SQL or EXEC ADO statements as these only work with generic ADO classes that are not bound to a particular database vendors product.

It is however supported by using the .NET Framework classes along with the Oracle Data Provider class Oracle.DataAccess as shown below.
The source for the stored procedure appears after the sample program and it must be run using Oracle PL/SQL or some other tool before the application can be run.

The following is source code for a Visual COBOL 2010 managed console project:

      $set ilusing"System.Data"
      $set ilusing"Oracle.DataAccess.Client"
      $set ilusing"Oracle.DataAccess.Types" 
       program-id. Program1 as "ConsoleApplication2.Program1".
 
       data division.
       working-storage section.
       exec sql include sqlca.cpy end-exec.
       01 sqlcon    object.
       01 sqltran   object.
       01 con       type OracleConnection.
       01 cmd       type OracleCommand.
       01 param1    type OracleParameter.
       01 param2    type OracleParameter.
       01 paramval  string occurs 3 value "Visual" "COBOL" "World".
       01 paramlen  binary-long occurs 3 value 20 20 20.
       01 i binary-long.
       01 retarray  type OracleString occurs 3 times.
       01 any-key   pic x.
       procedure division.
           
       *> following connect could be done in earlier program in run-unit and shared with EXEC SQL statements.
           exec sql connect to test64 end-exec
           exec ado get connection into :sqlcon transaction into :sqltran end-exec
           set con to sqlcon as type OracleConnection
     
           set cmd to con::CreateCommand
           set cmd to new OracleCommand("MYPACK.MYSP", con)
           set cmd::CommandType to type CommandType::StoredProcedure
           set param1 to cmd::Parameters::Add("param1", type OracleDbType::Varchar2)
           set param2 to cmd::Parameters::Add("param2", type OracleDbType::Varchar2)
           set param1::CollectionType to type OracleCollectionType::PLSQLAssociativeArray
           set param2::CollectionType to type OracleCollectionType::PLSQLAssociativeArray
           set param1::Direction to type ParameterDirection::Input
           set param2::Direction to type ParameterDirection::Output
           set param1::Value to paramval
           set param2::Value to null
           
           *> Specify the maximum number of elements in the arrays
          *> and the maximum size of the varchar2
           set param1::Size to 3
           set param2::Size to 3
           set param1::ArrayBindSize to paramlen
           set param2::ArrayBindSize to paramlen
 
          *> Execute the statement and output the results
           invoke cmd::ExecuteNonQuery
           set retarray to param2::Value as type OracleString[]
           perform varying i from 1 by 1
              until i > 3    
                 display retarray(i)
                 display " "
                 accept any-key
           end-perform
           goback.
           
       end program Program1.
 
 
Code to create stored procedure:
            
CREATE or replace PACKAGE MYPACK AS
TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
          PROCEDURE MYSP(
            Param1 IN     AssocArrayVarchar2_t,
            Param3    OUT AssocArrayVarchar2_t);
          END MYPACK;
/
CREATE or REPLACE package body MYPACK as
  PROCEDURE MYSP(
  Param1 IN     AssocArrayVarchar2_t,
  Param3    OUT AssocArrayVarchar2_t)
  IS
  BEGIN
    Param3(1) := Param1(1);
    Param3(2) := Param1(2);
    Param3(3) := Param1(3);
  END MYSP;
END MYPACK;