0 Replies Latest reply on Nov 8, 2018 2:11 PM by c-bik

    OCI-21560 OCIObjectNew OCI_TYPECODE_TABLE

    c-bik

      Hi,

       

      I have a package defined as follows:

       

      DROP PACKAGE pkg_test;

      DROP TYPE ARRRECNR;

      DROP TYPE ARRRECDATA;

       

      CREATE OR REPLACE TYPE ARRRECNR IS TABLE OF NUMBER(8);

      /

      CREATE OR REPLACE TYPE ARRRECDATA IS TABLE OF VARCHAR2(4000);

      /

       

      CREATE OR REPLACE PACKAGE pkg_test

      IS

         PROCEDURE test_proc1 (

        p_RecordNr In ARRRECNR,

        p_RecordData In ARRRECDATA,

        p_RecCount OUT Number,

        p_outrecorddata OUT ARRRECDATA

        );

       

         PROCEDURE test_proc2 (

        p_n In Number,

        p_v In Varchar2,

        p_outn OUT Number,

        p_outv OUT Varchar2

        );

      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

              p_RecCount = 0;

              p_OutRecordData := ARRRECDATA();

         FOR I IN 1..p_RecordData.COUNT

         LOOP

                  p_OutRecordData.extend();

        p_OutRecordData(I) := p_RecordData(I) || ' ' || to_char(p_RecordNr(I));

        p_RecCount := p_RecCount + I;

         END LOOP;

         END test_proc1;

       

         PROCEDURE test_proc2 (

        p_n In Number,

        p_v In Varchar2,

        p_outn OUT Number,

        p_outv OUT Varchar2

        ) IS

         BEGIN

        p_outv := p_v || ' ' || to_char(p_n);

        p_outn := 1;

         END test_proc2;

      END pkg_test;

      /

       

      And now I am trying to call the following PL/SQL:

      BEGIN

      pkg_test.test_proc1 (P_RECORDNR => :P_RECORDNR, P_RECORDDATA => :P_RECORDDATA, P_RECCOUNT => :P_RECCOUNT, P_OUTRECORDDATA => :P_OUTRECORDDATA);

      END

      /

       

      In C as follows:

              status = OCIStmtPrepare2(..., &stmthp,...

                                   NULL /*const OraText *key*/,

                                   0 /*ub4 keylen*/,

                                   OCI_NTV_SYNTAX, OCI_DEFAULT);

          if (status != OCI_SUCCESS) { return -1; }

       

          OCIBind *bindpp_P_RECCOUNT = NULL;

          sword P_RECCOUNT = 0;

          status = OCIBindByName(stmthp, &bindpp_P_RECCOUNT, errhp, (text *)":P_RECCOUNT",

                                strlen(":P_RECCOUNT"), (ub1 *)&P_RECCOUNT,

                               (sword)sizeof(P_RECCOUNT), SQLT_INT, (dvoid *)0,

                               (ub2 *)0, (ub2)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);

          if (status != OCI_SUCCESS) { return -1; }

       

          OCIBind *bindpp_P_RECORDNR = NULL;

          status = OCIBindByName(stmthp, &bindpp_P_RECORDNR, errhp, (text *)":P_RECORDNR",

                               -1, NULL /*void *valuep*/,

                               (sb4)sizeof(OCITable *) /*sb4 value_sz*/,

                               SQLT_NTY /*ub2 dty*/,

                                NULL /*void *indp*/,

                                NULL /*ub2 *alenp*/,

                                NULL /*ub2 *rcodep*/,

                                0 /*ub4 maxarr_len*/,

                                NULL /*ub4 *curelep*/,

                               OCI_DEFAULT /*ub4 mode*/);

          if (status != OCI_SUCCESS) { return -1; }

       

          OCIBind *bindpp_P_RECORDDATA = NULL;

          status = OCIBindByName(stmthp, &bindpp_P_RECORDDATA, errhp, ":P_RECORDDATA", (sb4)-1,

                                NULL /*void *valuep*/, (sb4)sizeof(OCITable *) /*sb4 value_sz*/,

                               SQLT_NTY /*ub2 dty*/, NULL /*void *indp*/, NULL /*ub2 *alenp*/,

                                NULL /*ub2 *rcodep*/, 0 /*ub4 maxarr_len*/, NULL /*ub4 *curelep*/,

                               OCI_DEFAULT /*ub4 mode*/);

          if (status != OCI_SUCCESS) { return -1; }

       

          OCIBind *bindpp_P_OUTRECORDDATA = NULL;

          status = OCIBindByName(stmthp, &bindpp_P_OUTRECORDDATA, errhp, ":P_OUTRECORDDATA",(sb4)-1,

                                NULL /*void *valuep*/,(sb4)sizeof(OCITable *) /*sb4 value_sz*/,

                               SQLT_NTY /*ub2 dty*/,

                                NULL /*void *indp*/, NULL /*ub2 *alenp*/, NULL /*ub2 *rcodep*/,

                                0 /*ub4 maxarr_len*/, NULL /*ub4 *curelep*/, OCI_DEFAULT /*ub4 mode*/);

          if (status != OCI_SUCCESS) { return -1; }

       

          OCIType *ArrRecNr = NULL;

          status = OCITypeByName(envhp, errhp, svchp, NULL, 0,

                                "ARRRECNR", (ub4)strlen("ARRRECNR"),

                                NULL, 0, OCI_DURATION_SESSION,

                               OCI_TYPEGET_ALL, &ArrRecNr);

          if (status != OCI_SUCCESS || !ArrRecNr) { return -1; }


          OCIType *ArrRecData = NULL;

          status = OCITypeByName(envhp, errhp, svchp, NULL, 0,

                                "ARRRECDATA", (ub4)strlen("ARRRECDATA"),

                                NULL, 0, OCI_DURATION_SESSION,

                               OCI_TYPEGET_ALL, &ArrRecData);

          if (status != OCI_SUCCESS || !ArrRecData) { return -1; }


          OCITable *in_P_RECORDNR = NULL;

          status = OCIObjectNew(envhp, errhp, svchp,

                               OCI_TYPECODE_TABLE, bindpp_P_RECORDNR,

                                NULL, OCI_DURATION_SESSION,

                                TRUE, &in_P_RECORDNR);

          if (status != OCI_SUCCESS)

          {

              printf("\nOCIObjectNew(envhp %p, errhp %p, svchp %p,\n"

                "OCI_TYPECODE_TABLE %d, bindpp_P_RECORDNR %p\n"

                "NULL %p, OCI_DURATION_SESSION %d\n"

                "TRUE %d, &in_P_RECORDNR %p)\n",

               envhp, errhp, svchp,

               OCI_TYPECODE_TABLE, bindpp_P_RECORDNR,

                NULL, OCI_DURATION_SESSION,

                TRUE, &in_P_RECORDNR);

              return -1;

          }

       

      Which is giving me the following error while trying to OCIBindByName:

       

           OCI-21560: argument 4 is null, invalid, or out of range

       

      Server

      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

      PL/SQL Release 11.2.0.2.0 - Production

      CORE    11.2.0.2.0      Production

      TNS for Linux: Version 11.2.0.2.0 - Production

      NLSRTL Version 11.2.0.2.0 - Production

       

      Client

           instantclient_12_1

       

      I check all the input parameters and 4th isn't NULL:

      OCIObjectNew(envhp 0000027D223CE9B0, errhp 0000027D223FC590, svchp

      0000027D223FB808,

      OCI_TYPECODE_TABLE 248, bindpp_P_RECORDNR 0000027D23D6B1B0

      NULL 0000000000000000, OCI_DURATION_SESSION 10

      TRUE 1, &in_P_RECORDNR 000000BCD47FFE00)

       

      What am I doing wrong?

       

      TIA

      Bikram