1 2 Previous Next 15 Replies Latest reply: Nov 8, 2010 2:39 AM by 642448 RSS

    A Bug in 11.1 array interface

    471042
      I have just encounter some code that runs fine in Oracle 9 to 10 but in 11.1 I am getting a different result.

      basically I have this SQL

      "INSERT INTO $table ( row_1, row_2, row_3) VALUES (?,?,?)"

      and bind all three to this array

      @var2 = (2,2,2,2,'s',2,2,2,2,2);

      using OCIBindByName and OCIBindDynamic and then execute them with
      OCIStmtExecute, with a mode (IN)
      of OCI_BATCH_ERRORS OCI_COMMIT_ON_SUCCESS (or 160)

      what happens is the commit does not happen in 11 but does happen in 9~10?

      Did something change in the execute in 11 or should I raise this up to a bug status??
        • 1. Re: A Bug in 11.1 array interface
          prajithparan
          This is looks strange . Can U paste the code for further analysis.
          Hope you are explicitly commiting with OCITransCommit
          • 2. Re: A Bug in 11.1 array interface
            471042
            Ok here is a trace of the OCI Calls I use, and yes I the last one I give is a OCITransCommit

            st_execute_array INSERT count=10 (ARRAY(0x1a725c8) ARRAY(0x1a934c4) undef)

            OCIBindByName(876dad8,19cab00,8755e68,":p1",3,0,1,1,0,0,0,0,0,DATA_AT_EXEC)=SUCCESS

            OCIBindDynamic(876eda8,8755e68,19caadc, phs_in,19caadc,phs_out)=SUCCESS
            OCIBindByName(876dad8,1a27810,8755e68,":p2",3,0,1,1,0,0,0,0,0,DATA_AT_EXEC)=SUCCESS
            OCIBindDynamic(876ec68,8755e68,1a277ec,phs_in,1a277ec,phs_out)=SUCCESS
            OCIBindByName(876dad8,1a2bc18,8755e68,":p3",3,0,1,1,0,0,0,0,0,DATA_AT_EXEC)=SUCCESS
            OCIBindDynamic(876eb28,8755e68,1a2bbf4,phs_in,1a2bbf4,phs_out)=SUCCESS

            OCIStmtExecute(8755db0,876dad8,8755e68,10,0,0,0,160)=SUCCESS_WITH_INFO ->(OCI_BATCH_ERRORS|OCI_COMMIT_ON_SUCCESS)<--
            OCIAttrGet(876dad8,OCI_HTYPE_STMT,140f4ec,0,129,8755e68)=SUCCESS
            OCIErrorGet(8755e68,1,"<NULL>",140f48c,"ORA-24381: error(s) in array DML",1024,2)=SUCCESS
            OCIErrorGet(8755e68,2,"<NULL>",140f48c,"ORA-24381: error(s) in array DML",1024,2)=NO_DATA
            OCIAttrGet(876dad8,OCI_HTYPE_STMT,140f55c,0,73,8755e68)=SUCCESS
            st_execute_array 1 errors in batch.
            OCIHandleAlloc(8727940,140f578,OCI_HTYPE_ERROR,0,0)=SUCCESS
            OCIHandleAlloc(8727940,140f570,OCI_HTYPE_ERROR,0,0)=SUCCESS
            OCIParamGet(8755e68,2,876c3c0,140f578,0)=SUCCESS
            OCIAttrGet(876c968,OCI_HTYPE_ERROR,140f550,0,74,8755e68)=SUCCESS
            st_execute_array error in row 4.
            OCIErrorGet(876c968,1,"<NULL>",140f4cc,"ORA-01722: invalid number",1024,2)=SUCCESS
                 OCIErrorGet(876c968,2,"<NULL>",140f4cc,"ORA-01722: invalid number",1024,2)=NO_DATA
            OCIHandleFree(876c3c0,OCI_HTYPE_ERROR)=SUCCESS
            OCIHandleFree(876c968,OCI_HTYPE_ERROR)=SUCCESS
            OCITransCommit(8755db0,8755e68,0)=SUCCESS

            st_execute_array warning: ORA-24381: error(s) in array DML (SUCCESS_WITH_
            INFO: error possibly near <*> indicator at char 56 in 'INSERT INTO test_ea( row_1, row_2,
            row_3) VALUES (:p1,:<*>p2,:p3)') [for Statement "INSERT INTO test_ea( row_1,  row_2, row_
            3) VALUES (?,?,?)"]



            and here is the code snipits but it is take out of a larger program so I will not run




            sb4
            phs_in(dvoid octxp, OCIBind bindp, ub4 iter, ub4 index,
                 dvoid **bufpp, ub4 alenp, ub1 piecep, dvoid **indpp)
            {
                 phs_t phs = (phs_t)octxp;
            STRLEN phs_len;
            AV *tuples_av;
                 SV *sv;
                 AV *av;
                 SV **sv_p;

                 /* Check for bind values supplied by tuple array. */
                 tuples_av = phs->imp_sth->bind_tuples;
                 if(tuples_av) {
                      /* NOTE: we already checked the validity in ora_st_bind_for_array_exec(). */
                      sv_p = av_fetch(tuples_av, phs->imp_sth->rowwise ? (int)iter : phs->idx, 0);
                      av = (AV*)SvRV(*sv_p);
                      sv_p = av_fetch(av, phs->imp_sth->rowwise ? phs->idx : (int)iter, 0);
                      sv = *sv_p;
                      if(SvOK(sv)) {
                      *bufpp = SvPV(sv, phs_len);
                      phs->alen = (phs->alen_incnull) ? phs_len+1 : phs_len;
                      phs->indp = 0;
                      } else {
                      *bufpp = SvPVX(sv);
                      phs->alen = 0;
                      phs->indp = -1;
                      }
                 }
            *alenp  = phs->alen;
            *indpp  = &phs->indp;
            *piecep = OCI_ONE_PIECE;

            if (!tuples_av && (index > 0 || iter > 0))
                      croak(" Arrays and multiple iterations not currently supported (in %d/%d)", index,iter);

            return OCI_CONTINUE;

            }

            sb4
            phs_out(dvoid octxp, OCIBind bindp,
                 ub4 iter,     /* execution itteration (0...)     */
                 ub4 index,     /* array index (0..)          */
                 dvoid **bufpp,     /* A pointer to a buffer to write the bind value/piece.     */
                 ub4 **alenpp,     /* A pointer to a storage for OCI to fill in the size     */
                           /* of the bind value/piece after it has been read.     */
                 ub1 piecep,     / */
                 dvoid **indpp,     /* Return a pointer to contain the indicator value which either an sb2     */
                           /* value or a pointer to an indicator structure for named data types.     */
                 ub2 **rcodepp)     /* Returns a pointer to contains the return code.     */
            {
            phs_t phs = (phs_t)octxp;     /* context */

            if (phs->desc_h) { /* a  descriptor if present  (LOBs etc)*/
                      *bufpp  = phs->desc_h;
                      phs->alen = 0;

            } else {
                      SV *sv = phs->sv;

                      if (SvTYPE(sv) == SVt_RV && SvTYPE(SvRV(sv)) == SVt_PVAV) {
                      sv = av_fetch((AV)SvRV(sv), (IV)iter, 1);
                      if (!SvOK(sv))
                                sv_setpv(sv,"");
                      }

                      bufpp = SvGROW(sv, (size_t)(((phs->maxlen < 28) ? 28 : phs->maxlen)+1)/for null*/);
                      phs->alen = SvLEN(sv);     /* max buffer size now, actual data len later */

            }
            *alenpp = &phs->alen;
            *indpp  = &phs->indp;
            *rcodepp= &phs->arcode;
            *piecep = OCI_ONE_PIECE;
            return OCI_CONTINUE;
            }



            static int
            do_bind_array_exec(sth, imp_sth, phs)
            SV *sth;
            imp_sth_t *imp_sth;
            phs_t *phs;
            {
                 dTHX;
            sword status;
            OCIBindByName_log(imp_sth->stmhp, &phs->bndhp, imp_sth->errhp,
            (text*)phs->name, (sb4)strlen(phs->name),
            0,
            phs->maxlen ? (sb4)phs->maxlen : 1, /* else bind "" fails */
            (ub2)phs->ftype, 0,
            NULL, /* ub2 alen_ptr not needed with OCIBindDynamic /
            0,
            0, /* max elements that can fit in allocated array */
            NULL, /* (ptr to) current number of elements in array */
            (ub4)OCI_DATA_AT_EXEC,
            status);
            if (status != OCI_SUCCESS) {
            oci_error(sth, imp_sth->errhp, status, "OCIBindByName");
            return 0;
            }


            OCIBindDynamic_log(phs->bndhp, imp_sth->errhp,
            (dvoid *)phs, phs_in,
            (dvoid *)phs, phs_out, status);
            if (status != OCI_SUCCESS) {
            oci_error(sth, imp_sth->errhp, status, "OCIBindDynamic");
            return 0;
            }
            return 1;
            }


            static void
            init_bind_for_array_exec(phs)
            phs_t *phs;
            {
                 dTHX;
            if (phs->sv == &sv_undef) { /* first bind for this placeholder  */
            phs->is_inout = 0;
            phs->maxlen = 1;
            /* treat Oracle7 SQLT_CUR as SQLT_RSET for Oracle8 */
            if (phs->ftype==102)
            phs->ftype = 116;
            /* some types require the trailing null included in the length. */
            /* SQLT_STR=5=STRING, SQLT_AVC=97=VARCHAR */
            phs->alen_incnull = (phs->ftype==SQLT_STR || phs->ftype==SQLT_AVC);
            }
            }

            int
            st_execute_array(sth, imp_sth, tuples, tuples_status, columns, exe_count)
            SV *sth;
            imp_sth_t *imp_sth;
            SV *tuples;
            SV *tuples_status;
            SV *columns;
            ub4 exe_count;
            {
                 sword status, exe_status;
            int is_select = (imp_sth->stmt_type == OCI_STMT_SELECT);
            AV tuples_av, tuples_status_av, *columns_av;
            ub4 oci_mode;
            ub4 num_errs;
            int i,j;
            int autocommit = 1;
            SV **sv_p;
                 phs_t **phs;
                 SV *sv;
                 AV *av;
            int param_count;
            char namebuf[30];
            STRLEN len;
            int outparams = (imp_sth->out_params_av) ? AvFILL(imp_sth->out_params_av)+1 : 0;

            tuples_av = (AV*)SvRV(tuples);

            /* Check the `columns' parameter. */
            if(SvTRUE(columns)) {
            if(!SvROK(columns) || SvTYPE(SvRV(columns)) != SVt_PVAV) {
            croak("ora_st_execute_array(): columns not an array peference.");
            }
            columns_av = (AV*)SvRV(columns);
            } else {
            columns_av = NULL;
            }
            /* Check the `tuples_status' parameter. */
            if(SvTRUE(tuples_status)) {
            if(!SvROK(tuples_status) || SvTYPE(SvRV(tuples_status)) != SVt_PVAV) {
                 croak("ora_st_execute_array(): tuples_status not an array reference.");
            }
            tuples_status_av = (AV*)SvRV(tuples_status);
            av_fill(tuples_status_av, exe_count - 1);
            /* Fill in 'unknown' exe count in every element (know not how to get
            individual execute row counts from OCI). */
            for(i = 0; (unsigned int) i < exe_count; i++) {
            av_store(tuples_status_av, i, newSViv((IV)-1));
            }
            } else {
            tuples_status_av = NULL;
            }

            /* Nothing to do if no tuples. */
            if(exe_count <= 0)
            return 0;


            param_count=c_NUM_PARAMS(imp_sth);/*returns the # of parameters on the imp_sth struct*/

                 phs = safemalloc(param_count*sizeof(*phs));
            memset(phs, 0, param_count*sizeof(*phs));

                 for(j = 0; (unsigned int) j < exe_count; j++) {

                 sv_p = av_fetch(tuples_av, j, 0);
            if(sv_p == NULL) {
            Safefree(phs);
            croak("Cannot fetch tuple %d", j);
            }
            sv = *sv_p;
            if(!SvROK(sv) || SvTYPE(SvRV(sv)) != SVt_PVAV) {
            Safefree(phs);
            croak("Not an array ref in element %d", j);
            }
            av = (AV*)SvRV(sv);
            for(i = 0; i < param_count; i++) {
            if(!phs) {
            SV **phs_svp;
            sprintf(namebuf, ":p%d", i+1);
            phs_svp = hv_fetch(imp_sth->all_params_hv,
            namebuf, strlen(namebuf), 0);
            if (phs_svp == NULL) {
            Safefree(phs);
            croak("Can't execute for non-existent placeholder :%d", i);
            }
            phs[i] = (phs_t*)(void*)SvPVX(*phs_svp); /* placeholder struct */
            if(phs[i]->idx < 0) {
            Safefree(phs);
            croak("Placeholder %d not of ?/:1 type", i);
            }
            init_bind_for_array_exec(phs[i]); /*bind the value */
            }
            sv_p = av_fetch(av, phs[i]->idx, 0);
            if(sv_p == NULL) {
            Safefree(phs);
            croak("Cannot fetch value for param %d in entry %d", i, j);
                 }

                           sv = *sv_p;

                 /*check to see if value sv is a null (undef) if it is upgrade it*/
                           if (!SvOK(sv))     {
                                if(SvUPGRADE(sv, SVt_PV)){} /* For GCC not to warn on unused result */
                           }
                           else {
                      SvPV(sv, len);
                 }


                 /* Find the value length, and increase maxlen if needed. */
                 if(SvROK(sv)) {
                 Safefree(phs);
                 croak("Can't bind a reference (%s) for param %d, entry %d",
                 neatsvpv(sv,0), i, j);
                 }
                 if(len > (unsigned int) phs[i]->maxlen)
                 phs[i]->maxlen = len;

                 /* Do OCI bind calls on last iteration. */
                 if( ((unsigned int) j ) == exe_count - 1 ) {
                 if(!do_bind_array_exec(sth, imp_sth, phs[i])) {
                 Safefree(phs);
                      }
                           }
                 }
                 }
                 Safefree(phs);

            /* Store array of bind typles, for use in OCIBindDynamic() callback. */
            imp_sth->bind_tuples = tuples_av;
            imp_sth->rowwise = (columns_av == NULL);

            oci_mode = OCI_BATCH_ERRORS;

            if(autocommit)
            oci_mode |= OCI_COMMIT_ON_SUCCESS;

                 OCIStmtExecute_log(imp_sth->svchp, imp_sth->stmhp, imp_sth->errhp,
            exe_count, 0, 0, 0, oci_mode, exe_status);

                 imp_sth->bind_tuples = NULL;

            if (exe_status != OCI_SUCCESS) {
                      oci_error(sth, imp_sth->errhp, exe_status, ora_sql_error(imp_sth,"OCIStmtExecute"));
            if(exe_status != OCI_SUCCESS_WITH_INFO)
            return -2;
            }

            OCIAttrGet_log(imp_sth, &num_errs, 0, OCI_ATTR_NUM_DML_ERRORS, status);

            if(num_errs && tuples_status_av) {
            OCIError row_errhp, tmp_errhp;
            ub4 row_off;
            SV *err_svs[2];
            /*AV err_av;/
            sb4 err_code;

            err_svs[0] = newSViv((IV)0);
            err_svs[1] = newSVpvn("", 0);
            OCIHandleAlloc_log(imp_sth->envhp, &row_errhp, OCI_HTYPE_ERROR, status);
            OCIHandleAlloc_log(imp_sth->envhp, &tmp_errhp, OCI_HTYPE_ERROR, status);
            for(i = 0; (unsigned int) i < num_errs; i++) {
            OCIParamGet_log(imp_sth->errhp, OCI_HTYPE_ERROR,
            tmp_errhp, (dvoid *)&row_errhp,
            (ub4)i, status);
            OCIAttrGet_log(row_errhp, OCI_HTYPE_ERROR, &row_off, 0,
            OCI_ATTR_DML_ROW_OFFSET, imp_sth->errhp, status);
            sv_setpv(err_svs[1], "");
            err_code = oci_error_get(row_errhp, exe_status, NULL, err_svs[1], debug);
            sv_setiv(err_svs[0], (IV)err_code);
            av_store(tuples_status_av, row_off,
            newRV_noinc((SV *)(av_make(2, err_svs))));
            }
            OCIHandleFree_log(tmp_errhp, OCI_HTYPE_ERROR, status);
            OCIHandleFree_log(row_errhp, OCI_HTYPE_ERROR, status);

            /* Do a commit here if autocommit is set, since Oracle
            doesn't do that for us when some rows are in error. */
            if(autocommit) {
            OCITransCommit_log(imp_sth->svchp, imp_sth->errhp,
            OCI_DEFAULT, status);
            if (status != OCI_SUCCESS) {
            oci_error(sth, imp_sth->errhp, status, "OCITransCommit");
            return -2;
            }
            }
            }

            if(num_errs) {
            return -2;
            } else {
            ub4 row_count = 0;
                      OCIAttrGet_stmhp_log(imp_sth, &row_count, 0, OCI_ATTR_ROW_COUNT, status);
            return row_count;
            }
            }
            • 3. Re: A Bug in 11.1 array interface
              prajithparan
              It looks like it goes to exception before commit and nothing is inserted.
              ORA-01722 is raised due to data (string to numeric) check the data types are same in both 11 and 10 in tables
              • 4. Re: A Bug in 11.1 array interface
                471042
                It looks like it goes to exception before commit and
                nothing is inserted.
                No it falls through and just reports the error if it is 'success_with_info' (though the above code may be a little off as it is cut out of a much larger codebase.
                ORA-01722 is raised due to data (string to numeric)
                check the data types are same in both 11 and 10 in
                tables
                Yes the data types are the same. This is actually a test case that is deliberately trying to fail on insert, that is how I caught it.

                One of the values passed in is wrong but the 9 others are OK and should be committed. This happens in 9 and 10 but not 11.

                The key point me thinks is passing in OCI_BATCH_ERRORS|OCI_COMMIT_ON_SUCCESS) on the execute


                oci_mode = OCI_BATCH_ERRORS;
                if(autocommit)
                oci_mode |= OCI_COMMIT_ON_SUCCESS;

                OCIStmtExecute_log(imp_sth->svchp, imp_sth->stmhp, imp_sth->errhp,exe_count, 0, 0, 0, oci_mode, exe_status);


                the trace output for the execute is

                OCIStmtExecute(8755db0,876dad8,8755e68,10,0,0,0,160=SUCCESS_WITH_INFO

                160 is the value passed in and in 9+10 with commits but in 11 it does not.
                • 5. Re: A Bug in 11.1 array interface
                  532463
                  Hi,

                  I tried to replicate the problem but it seems to be working in my case. Could you please try this if you havent already: Using your same program, please try to insert a set of rows which do not result in any constraint violation and see if that goes through fine.

                  Thanks,
                  Sumit
                  • 6. Re: A Bug in 11.1 array interface
                    471042
                    Yes I have run it with both good and bad data it is actually part of a test case of a large system and in 11g it is failing the test.

                    The test starts with an empty table and then an insert of

                    @var2 = (2,2,2,2,2,2,2,2,2,2);

                    which should add ten records to the table. The test checks the # of records in the table and passes with 10.

                    the next test is an insert of this array

                    @var2 = (2,2,2,2,'s',2,2,2,2,2);

                    which should add 9 records with 1 fail. So I theory I should have 19 records in my table. However, on 11 I only have 10 records in the table not the 19 it is expecting so the code does not add 9 records like it should, it rolls them all back.

                    Like I said before work fine in 9 and 10 but fails in 11.

                    The 11 bug is in the OCIStmExecute

                    OCIStmtExecute(8755db0,876dad8,8755e68,10,0,0,0,160);

                    I send it a Mode in value of 160 which is OCI_BATCH_ERRORS and OCI_COMMIT_ON_SUCCESS

                    This should commit any good inserts and batch the errors of any bad ones at least that is how it worked in 9 and 10 but it does not do that in 11.

                    I can't make it any simpler than that. Something has changed in 11 to make it work differently.
                    • 7. Re: A Bug in 11.1 array interface
                      prajithparan
                      Did you able to trace in SUCCESS_WITH_INFO what is the Error/Info text coming from server.
                      There is a chance that the data is truncated.
                      • 8. Re: A Bug in 11.1 array interface
                        471042
                        I will check that but I do not think one would get a truncate with a single letter or digit.
                        • 9. Re: A Bug in 11.1 array interface
                          127202
                          Hi,

                          Did you found any solutions for this issue?

                          Thanks.
                          • 10. Re: A Bug in 11.1 array interface
                            471042
                            I have been digging into the bug further today and there is no doubt about it something is not working correctly in 11

                            I took the above code and and changed it for a direct commit and it fails and does not enter the extra 9 records.

                            Change the code again so it is just an arrays of 2's and it works with no problem

                            looking at these two OCI traces the first is will fail (on a 11 DB) and the second(10XE) will pass

                            There are no differences in the code trace.

                            I also reran the code with an explicit commit later on and as I expected the records were not added.

                            So there must be some sort of bug in 11g

                            Trace 1

                            OCIStmtExecute(10076dfc0,10078b470,10076e098,10,0,0,0,mode=(UNKNOWN OCI EXECUTE MODE 160),160)=SUCCESS_WITH_INFO
                            OCIAttrGet(10078b470,OCI_HTYPE_STMT,ffffffff7fffe1d6,0,129,10076e098)=SUCCESS
                            OCIErrorGet(10076e098,1,"<NULL>",ffffffff7fffdd6c,"ORA-24381: error(s) in array DML
                            ",1024,2)=SUCCESS
                            OCIErrorGet(10076e098,2,"<NULL>",ffffffff7fffdd6c,"ORA-24381: error(s) in array DML
                            ",1024,2)=NO_DATA
                            OCIAttrGet(10078b470,OCI_HTYPE_STMT,ffffffff7fffe4bc,0,73,10076e098)=SUCCESS
                            ora_st_execute_array 1 errors in batch.
                            OCIHandleAlloc(10072eca0,ffffffff7fffe480,OCI_HTYPE_ERROR,0,0)=SUCCESS
                            OCIHandleAlloc(10072eca0,ffffffff7fffe478,OCI_HTYPE_ERROR,0,0)=SUCCESS
                            OCIParamGet(10076e098,2,100789dc0,ffffffff7fffe480,0)=SUCCESS
                            OCIAttrGet(10078a430,OCI_HTYPE_ERROR,ffffffff7fffe48c,0,74,10076e098)=SUCCESS
                            ora_st_execute_array error in row 4.
                            OCIErrorGet(10078a430,1,"<NULL>",ffffffff7fffde3c,"ORA-01722: invalid number
                            ",1024,2)=SUCCESS
                            OCIErrorGet after <NULL> (er1:ok): 1, 1722: ORA-01722: invalid number

                            OCIErrorGet(10078a430,2,"<NULL>",ffffffff7fffde3c,"ORA-01722: invalid number
                            ",1024,2)=NO_DATA
                            OCIHandleFree(100789dc0,OCI_HTYPE_ERROR)=SUCCESS
                            OCIHandleFree(10078a430,OCI_HTYPE_ERROR)=SUCCESS
                            OCITransCommit(10076dfc0,10076e098,0)=SUCCESS

                            Trace 2

                            OCIStmtExecute(39a2c44,39aa148,39a2cf8,10,0,0,0,mode=(UNKNOWN OCI EXECUTE MODE 160),160)=SUCCESS_WITH_INFO
                            OCIAttrGet(39aa148,OCI_HTYPE_STMT,140f4a4,0,129,39a2cf8)=SUCCESS
                            OCIErrorGet(39a2cf8,1,"<NULL>",140f444,"ORA-24381: error(s) in array DML
                            ",1024,2)=SUCCESS
                            OCIErrorGet(39a2cf8,2,"<NULL>",140f444,"ORA-24381: error(s) in array DML
                            ",1024,2)=NO_DATA
                            OCIAttrGet(39aa148,OCI_HTYPE_STMT,140f51c,0,73,39a2cf8)=SUCCESS
                            ora_st_execute_array 1 errors in batch.
                            OCIHandleAlloc(398d420,140f548,OCI_HTYPE_ERROR,0,0)=SUCCESS
                            OCIHandleAlloc(398d420,140f53c,OCI_HTYPE_ERROR,0,0)=SUCCESS
                            OCIParamGet(39a2cf8,2,39a89ec,140f548,0)=SUCCESS
                            OCIAttrGet(39a8f94,OCI_HTYPE_ERROR,140f520,0,74,39a2cf8)=SUCCESS
                            ora_st_execute_array error in row 4.
                            OCIErrorGet(39a8f94,1,"<NULL>",140f484,"ORA-01722: invalid number
                            ",1024,2)=SUCCESS
                            OCIErrorGet after <NULL> (er1:ok): 1, 1722: ORA-01722: invalid number

                            OCIErrorGet(39a8f94,2,"<NULL>",140f484,"ORA-01722: invalid number


                            ",1024,2)=NO_DATA
                            OCIHandleFree(39a89ec,OCI_HTYPE_ERROR)=SUCCESS
                            OCIHandleFree(39a8f94,OCI_HTYPE_ERROR)=SUCCESS
                            OCITransCommit(39a2c44,39a2cf8,0)=SUCCESS
                            • 11. Re: A Bug in 11.1 array interface
                              471042
                              I friend of my wrote a little c program that will demonstrate the bug

                              You can find it here

                              http://markmail.org/download.xqy?id=4hq6fjthcpexukva&number=1

                              Edited by: user468039 on Jan 20, 2009 10:06 AM
                              • 12. Re: A Bug in 11.1 array interface
                                cj
                                What is your actual and expected output from the insert_array program?

                                I compiled it with 10gR2 and 11.1.0.6 clients. I saw no difference in output when either executable connected to (remote) 10gR2 or 11.1.0.6 databases.

                                Knowing your platform and exact Oracle versions is always helpful, too.
                                • 13. Re: A Bug in 11.1 array interface
                                  468439
                                  I am getting this error too and it refers to the test t/26exe_array.t in DBD::Oracle 1.23. The problem is as described previously in that the code is intended to insert 10 rows but one row contains an invalid value. With DBD::Oracle 1.23 and Oracle XE or Oracle 10 it passes the test fine and inserts 9 of the 10 rows. With oracle 11 none of the rows are inserted.

                                  My Oracle server is "Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production" running on Linux CentOS release 5 (Final).

                                  I have tried the standalone test code mentioned above but I don't think it is demonstrating the problem in my case - I'm not sure what the expected output is but running it on both XE and 11 I get the same results and 9 rows inserted in the foo table. I've even run it from a machine with oracle10g installed (using that set of client libs) to 11 server (which is what I'm doing with DBD::Oracle) and it still appears to work.

                                  So am I now suspicious that the standalone code above is not duplicating what DBD::Oracle is doing. If anyone gets a different output from the standalone code above I'd like to hear about it. My output is as follows with 9 rows inserted afterwards:

                                  ./insert_array
                                  Environment setup ....
                                  Logging on as user ....
                                  user logged on.
                                  FAILED: OCIStmtExecute() insert
                                  ERROR CODE = 24381
                                  ORA-24381: error(s) in array DML



                                  DEMONSTRATING INSERT....RETURNING

                                  *** ITERATION *** : 0
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 1
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 2


                                  *** ITERATION *** : 1
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 2
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 3


                                  *** ITERATION *** : 2
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 3
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 4


                                  *** ITERATION *** : 3
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 4
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 5


                                  *** ITERATION *** : 4
                                  (...returning 0 rows)

                                  *** ITERATION *** : 5
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 6
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 7


                                  *** ITERATION *** : 6
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 7
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 8


                                  *** ITERATION *** : 7
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 8
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 9


                                  *** ITERATION *** : 8
                                  (...returning 1 rows)
                                  COL1 [0]: ind = 0, rc = 0, retl = 4
                                  COL1 [0]: 9
                                  COL2 [0]: ind = 0, rc = 0, retl = 4
                                  COL2 [0]: 10


                                  *** ITERATION *** : 9
                                  (...returning 0 rows)
                                  FAILED: demo_insert()
                                  user Logged off.
                                  Detached from server.
                                  Freeing handles ...
                                  • 14. Re: A Bug in 11.1 array interface
                                    807586
                                    I am experiencing a problem with almost the same characteristics. Have you found a solution to this?

                                    In short, in Oracle 9 & 10, we create an array of bind variables and when the SQL statement is executed, it acts like each bind variable is executed by itself.

                                    In Oracle 11, the same SQL statement acts like a BULK execution where a single error in one of the bind variables causes the entire set of bind variables to be rejected.

                                    In researching this, I found the ProC directives max_row_insert to try and control this but I can't find anything that affects this with OCI.

                                    Any information that you have on this would be appreciated.

                                    Gary
                                    1 2 Previous Next