Support for associative array

[shamis]

Following is a an example

    Type ArrRecNr                   Is Table Of Number(8)       Index By Binary_Integer;
    Type ArrRecData                 Is Table Of Varchar2(4000)  Index By Binary_Integer;

  Procedure SP_INSERT_CSV (
    p_BiHId                     In      VarChar2,
    p_BatchSize                 In      Integer,
    p_MaxAge                    In      Number,
    p_DataHeader                In      VarChar2,

    p_RecordNr                  In      ArrRecNr,
    p_RecordData                In      ArrRecData,

    p_RecCount                  In Out  Number,
    p_PreParseErrCount          In Out  Number,
    p_ErrCount                  In Out  Number,
    p_DateFc                    In Out  Varchar2,
    p_DateLc                    In Out  Varchar2,
    p_ErrorCode                 Out     Number,
    p_ErrorDesc                 Out     Varchar2,
    p_ReturnStatus              Out     Number
)

Following procedure call uses these above two types as input arguments.

DECLARE
  P_BIHID VARCHAR2(200);
  P_BATCHSIZE NUMBER;
  P_MAXAGE NUMBER;
  P_DATAHEADER VARCHAR2(200);
  P_RECORDNR SCOTT.PKG_MEC_IC_CSV.ARRRECNR;
  P_RECORDDATA SCOTT.PKG_MEC_IC_CSV.ARRRECDATA;
  P_RECCOUNT NUMBER;
  P_PREPARSEERRCOUNT NUMBER;
  P_ERRCOUNT NUMBER;
  P_DATEFC VARCHAR2(200);
  P_DATELC VARCHAR2(200);
  P_ERRORCODE NUMBER;
  P_ERRORDESC VARCHAR2(200);
  P_RETURNSTATUS NUMBER;
BEGIN
  p_bihid := '123456';
  P_BATCHSIZE := 3;
  p_maxage := 5;
  p_dataheader := 'test';
  p_recordnr(1) := 1;
  p_recordnr(2) := 2;
  p_recordnr(3) := 3;
  P_RECORDDATA(1) := 'test1';
  p_recorddata(2) := 'test2';
  P_RECORDDATA(3) := 'test3';
  P_RECCOUNT := NULL;
  P_PREPARSEERRCOUNT := NULL;
  P_ERRCOUNT := NULL;
  P_DATEFC := NULL;
  P_DATELC := NULL;

  PKG_MEC_IC_CSV.SP_INSERT_CSV(
    P_BIHID => P_BIHID,
    P_BATCHSIZE => P_BATCHSIZE,
    P_MAXAGE => P_MAXAGE,
    P_DATAHEADER => P_DATAHEADER,
    P_RECORDNR => P_RECORDNR,
    P_RECORDDATA => P_RECORDDATA,
    P_RECCOUNT => P_RECCOUNT,
    P_PREPARSEERRCOUNT => P_PREPARSEERRCOUNT,
    P_ERRCOUNT => P_ERRCOUNT,
    P_DATEFC => P_DATEFC,
    P_DATELC => P_DATELC,
    P_ERRORCODE => P_ERRORCODE,
    P_ERRORDESC => P_ERRORDESC,
    P_RETURNSTATUS => P_RETURNSTATUS
  );
END;

https://blogs.oracle.com/oraclemagazine/working-with-collections

[shamis]

Simple array fun

create or replace FUNCTION test_fun(a IN NUMBER)
RETURN T_TEXT_ARR
IS
p_text T_TEXT_ARR := T_TEXT_ARR();
BEGIN
    FOR I IN 1..A
    LOOP
        p_text.extend;
        p_text(I) := 'Test' || to_char(I);
    END LOOP;
    --p_text := T_TEXT_ARR('Hello','world','!');
    RETURN(p_text);
END test_fun;

Function can be invoked using the following select sql

select test_fun(7) from dual;

[shamis]

Sample procedure using associative array

CREATE OR REPLACE PROCEDURE test_proc1(
    p_RecordNr In SCOTT.PKG_MEC_IC_CSV.ArrRecNr,
    p_RecordData In SCOTT.PKG_MEC_IC_CSV.ArrRecData,
    p_RecCount OUT Number,
    p_outrecorddata OUT scott.pkg_mec_ic_csv.arrrecdata
) IS
BEGIN
    FOR I IN 1..p_RecordData.COUNT
    LOOP
        p_outrecorddata(I) := p_recorddata(I) || ' ' || to_char(p_recordnr(I));
        p_RecCount := I;
    END LOOP;
END;

To invoke the procedure use the following

DECLARE
    p_recordnr        scott.pkg_mec_ic_csv.arrrecnr;
    p_recorddata      scott.pkg_mec_ic_csv.arrrecdata;
    p_reccount        NUMBER;
    p_outrecorddata   scott.pkg_mec_ic_csv.arrrecdata;
BEGIN
    p_recordnr(1) := 1;
    p_recordnr(2) := 2;
    p_recordnr(3) := 3;
    p_recorddata(1) := 'test1';
    p_recorddata(2) := 'test2';
    p_recorddata(3) := 'test3';
    p_reccount := 1;
    test_proc1(
        p_recordnr        => p_recordnr,
        p_recorddata      => p_recorddata,
        p_reccount        => p_reccount,
        p_outrecorddata   => p_outrecorddata
    );

    dbms_output.put_line('P_RECCOUNT = ' || p_reccount);
    FOR i IN 1..p_reccount LOOP
        dbms_output.put_line('P_OUTRECORDDATA('
         || TO_CHAR(i)
         || ') = '
         || p_outrecorddata(i) );
    END LOOP;

END;

Following output is produced

P_RECCOUNT = 3
P_OUTRECORDDATA(1) = test1 1
P_OUTRECORDDATA(2) = test2 2
P_OUTRECORDDATA(3) = test3 3

[c-bik]

Simplified and generic test package with internal type definition and procedure using internal type as interface

CREATE OR REPLACE PACKAGE pkg_test
IS
    Type ArrBdReqNo Is Table Of Number(8)       Index By Binary_Integer;
    Type ArrRecNr   Is Table Of Number(8)       Index By Binary_Integer;
    Type ArrRecData Is Table Of Varchar2(4000)  Index By Binary_Integer;

    PROCEDURE test_proc1 (
        p_RecordNr In ArrRecNr,
        p_RecordData In ArrRecData,
        p_RecCount OUT Number,
        p_outrecorddata OUT arrrecdata
    );
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
IS
    PROCEDURE test_proc1 (
        p_RecordNr In ArrRecNr,
        p_RecordData In ArrRecData,
        p_RecCount OUT Number,
        p_OutRecordData OUT ArrRecData
    ) IS
    BEGIN
        FOR I IN 1..p_RecordData.COUNT
        LOOP
            p_OutRecordData(I) := p_RecordData(I) || ' ' || to_char(p_RecordNr(I));
            p_RecCount := I;
        END LOOP;
    END test_proc1 ;
END pkg_test;
/

[c-bik]

Already support (in production) https://github.com/K2InformaticsGmbH/oranif/blob/77a57ad203120ca6b3baecdddebf3e8646440fde/c_src/dpiConn_nif.c#L193-L212