5 Replies Latest reply on Feb 19, 2020 10:22 PM by cj

    Bug with OCI 12.1.0.2.0 and 12.2.0.1.0.

    Dmitriy Gulaev

      I found a bug in OCI.DLL 12.1.0.2.0 and 12.2.0.1.0.

      I have created examples in C and C# to reproduce bug

       

      Short description

      After call to OCIEnvCreate I can not correctly bind output parameter for pl-sql statement with type varchar.

      It must return UCS2 string,

      I use OCIAttrSet to set OCI_ATTR_CHARSET_ID with value 1000. It sets it and parameter value is returned as UCS2 string, But length of strin is in chars instead bytes.

      So, my sql: begin :param:='HELLO'; end;

      After executing it returns value in UCS2. Bytes: 'H','\0','E','\0','L','\0','L','\0','O','\0'], but length = 5 bytes (instead 10)

       

      If I use OCIEnvNlsCreate with char and nchar sets 0 (default), the all works fine (length=10 after OCIAttrSet)

      With OCI 11.2.0.3.0 and earlier all works fine in both cases (OCIEnvCreate and OCIEnvNlsCreate)

       

      Code:

      // OCI-BUG-C.cpp
      
      
      #include <stdio.h>
      #include <string.h>
      #include <stdlib.h>
      #include <oci.h>
      
      
      void checkerr(OCIError *errhp, sword status);
      
      
      int main(int argc, char* argv[])
      {
        text *dbname = "...";
        text *user = "...";
        text *password = "...";
        OCIEnv *envhp;
        OCIError *errhp;
        OCIServer *srvhp;
        OCISvcCtx *svchp;
        OCISession *authp;
        OCIStmt *stmthp;
        OCIBind *bindhp;
        text *stmt;
        text *pname;
        ub2 pvalue[1000];
        ub2 alen;
        sb2 indp;
        sb2 charset_id;
      
      
        printf("======== OCI-BUG-C Example (OCI) ========\n");
        // if we use OCIEnvNlsCreate, then all works correctly
        //checkerr(NULL, OCIEnvNlsCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL, 0, 0));
        // But .NET uses OCIEnvCreate. This will cause a error with length of output binding parameter (see below)
        checkerr(NULL, OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL));
      
      
        checkerr(NULL, OCIHandleAlloc(envhp, &errhp, OCI_HTYPE_ERROR, 0, NULL));
        checkerr(errhp, OCIHandleAlloc(envhp, &srvhp, OCI_HTYPE_SERVER, 0, NULL));
        checkerr(errhp, OCIHandleAlloc(envhp, &svchp, OCI_HTYPE_SVCCTX, 0, NULL));
        checkerr(errhp, OCIServerAttach(srvhp, errhp, dbname, strlen(dbname), OCI_DEFAULT));
        checkerr(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, srvhp, 0, OCI_ATTR_SERVER, errhp));
        checkerr(errhp, OCIHandleAlloc(envhp, &authp, OCI_HTYPE_SESSION, 0, NULL));
        checkerr(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, user, strlen(user), OCI_ATTR_USERNAME, errhp));
        checkerr(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, password, strlen(password), OCI_ATTR_PASSWORD, errhp));
        checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT));
        checkerr(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, authp, 0, OCI_ATTR_SESSION, errhp));
        checkerr(errhp, OCIHandleAlloc(envhp, &stmthp, OCI_HTYPE_STMT, 0, NULL));
        stmt = "begin :param:='HELLO'; end;";
        checkerr(errhp, OCIStmtPrepare(stmthp, errhp, stmt, strlen(stmt), OCI_NTV_SYNTAX, OCI_DEFAULT));
        pname = "param";
        alen = 0;
        indp = OCI_IND_NULL;
        checkerr(errhp, OCIBindByName(stmthp, &bindhp, errhp, pname, strlen(pname), pvalue, sizeof(pvalue)-sizeof(ub2), OCI_TYPECODE_VARCHAR, &indp, &alen, NULL, 0, NULL, OCI_DEFAULT));
      
      
        // We want UCS2 format. OCI >= 12 sets it but alen will recieve count of chars instead bytes
        // This occures only if OCIEnvCreate was used. With OCIEnvNlsCreate all works correctly
        charset_id = 1000; 
        checkerr(errhp, OCIAttrSet(bindhp, OCI_HTYPE_BIND, &charset_id, 0, OCI_ATTR_CHARSET_ID, errhp));
      
      
        checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, NULL, NULL, OCI_DEFAULT));
        switch (indp)
        {
        case OCI_IND_NOTNULL:
          printf("param length = %d bytes (%d UCS2 chars)%s\n",
            alen,
            alen/2,
            alen == 10 ? "" : " !!!ERROR!!!: param length should be 10 bytes");
          pvalue[alen/2]=L'?';
          pvalue[(alen+1)/2]=L'\0';
          printf("param value = '%ls'\n", pvalue);
          break;
        case OCI_IND_NULL:
          printf("param value = null\n");
          break;
        default:
          printf("INDICATOR = %d", indp);
          break;
        }
      
      
        return 0;
      }
      
      
      void checkerr(OCIError *errhp, sword status)
      {
        switch (status)
        {
        case OCI_SUCCESS:
          return;
        case OCI_SUCCESS_WITH_INFO:
          printf("Error - OCI_SUCCESS_WITH_INFO\n");
          break;
        case OCI_NEED_DATA:
          printf("Error - OCI_NEED_DATA\n");
          break;
        case OCI_NO_DATA:
          printf("Error - OCI_NODATA\n");
          break;
        case OCI_ERROR:
          if(errhp!=NULL)
          {
            text errbuf[512];
            sb4 errcode = 0;
            OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
              errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
            printf("Error - %.*s\n", 512, errbuf);
          }
          else
          {
            printf("Error - OCI_ERROR UNKNOWN\n");
          }
          break;
        case OCI_INVALID_HANDLE:
          printf("Error - OCI_INVALID_HANDLE\n");
          break;
        case OCI_STILL_EXECUTING:
          printf("Error - OCI_STILL_EXECUTE\n");
          break;
        case OCI_CONTINUE:
          printf("Error - OCI_CONTINUE\n");
          break;
        default:
          printf("Error - UNKNOWN\n");
        }
        exit(1);
      }
      

       

      Results:

      OCI 11.2.0.3.0
      ======== OCI-BUG-C Example (OCI) ========
      param length = 10 bytes (5 UCS2 chars)
      param value = 'HELLO'
      
      
      OCI 12.1.0.2.0
      ======== OCI-BUG-C Example (OCI) ========
      param length = 5 bytes (2 UCS2 chars) !!!ERROR!!!: param length should be 10 bytes
      param value = 'HE?'
      
      
      OCI 12.2.0.1.0
      ======== OCI-BUG-C Example (OCI) ========
      param length = 5 bytes (2 UCS2 chars) !!!ERROR!!!: param length should be 10 bytes
      param value = 'HE?'
      

       

      I can not use OCIEnvNlsCreate because, in general, it is not my code. It is .NET System.Data.OracleClient code. It uses OCIEnvCreate.

      Then Ms SSRS (Sql Server Reporting Services) uses .NET to connect to datasource and retrieve data

      As a result: SSRS does not work with OCI 12.1 and 12.2

      SSRS can not use datasets as procedure call because it executes:

      dbms_utility.name_resolve('PROCEDURE_NAME',1,:schema,:part1,:part2,:dblink,:part1type,:objectnum); end;

      And output parameters 'schema' and 'part2' (name) are returned incorrect due to incorrect string length.

       

      I have created project (VS2008) with this example in C an C#. I can send it to support center, but I do not know where

      Can somebody give me direct url to report bug or can somebody register this bug somewhere by himself?

       

      Thanks

        • 1. Re: Bug with OCI 12.1.0.2.0 and 12.2.0.1.0.
          cj

          OCIBindByName documentation says that:

           

          When the older OCI environment handle creation interfaces are used (either OCIEnvCreate()) or deprecated OCIEnvInit()), alenp lengths are in bytes in general. However, alenp lengths are expected in characters for IN binds and also reported in characters for OUT binds only when either the character set is OCI_UC2ID (= OCI_UTF16ID) or when OCI_ATTR_CHAR_COUNT attribute is set on the corresponding OCIBind handle.

          • 2. Re: Bug with OCI 12.1.0.2.0 and 12.2.0.1.0.
            Dmitriy Gulaev

            Hi,

            Thanks for reply

             

            You want to say that:

            When OCI_ATTR_CHARSET_ID is set to OCI_UTF16ID then semantics of "alen" is changed to "count of chars" instead "count of bytes".

            Yes?

             

            If so, this raises more questions:

             

            1. Why semantics of "alen" is "count of bytes" if I use "OCIEnvNlsCreate" (OCIEnvNlsCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL, 0, 0))?

             

            2. Why with OCI 11.2 semantics of "alen" is "count of bytes" in both cases, with "OCIEnvCreate" and "OCIEnvNlsCreate"?

            Does it mean that oracle has broken functionality compatibility in OCI 12.x with previous versions?

            Or does OCI 9-11 still contains bug with semantics of "alen" (always "count of bytes")?

             

            3. Why code example (https://docs.oracle.com/cd/B19306_01/appdev.102/b14250/oci05bnd.htm#sthref792) uses semantics of bytes and not chars?

            OCIBindByName (stmthp1, &bnd1p, errhp, (text*)":ENAME",
              (sb4)strlen((char *)":ENAME"),
              (dvoid *) ename, sizeof(ename), SQLT_STR,
              (dvoid *)&insname_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0,
              (ub4 *)0, OCI_DEFAULT);
            

            Look please at "sizeof(ename)"!

            "ename" is utext (2 bytes per char).

            sizeof(ename) = size in bytes!

             

            Very very strange and not friendly

            • 3. Re: Bug with OCI 12.1.0.2.0 and 12.2.0.1.0.
              cj

              You want to say that:

              When OCI_ATTR_CHARSET_ID is set to OCI_UTF16ID then semantics of "alen" is changed to "count of chars" instead "count of bytes".

              Yes?

              When using the older initialization call, that's how I read it.

              If so, this raises more questions:

               

              1. Why semantics of "alen" is "count of bytes" if I use "OCIEnvNlsCreate" (OCIEnvNlsCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL, 0, 0))?

               

              2. Why with OCI 11.2 semantics of "alen" is "count of bytes" in both cases, with "OCIEnvCreate" and "OCIEnvNlsCreate"?

              Does it mean that oracle has broken functionality compatibility in OCI 12.x with previous versions?

              Or does OCI 9-11 still contains bug with semantics of "alen" (always "count of bytes")?

              Since the change got properly documented, I assume there was a valid reason it was made. More pragmatically, considering that this is for the 'older' initialization function, that the same behavior occurs in 19c, and that 11.2 is well into its twilight years, I don't see anyone having the courage to change the behavior now.

              3. Why code example (https://docs.oracle.com/cd/B19306_01/appdev.102/b14250/oci05bnd.htm#sthref792) uses semantics of bytes and not chars?

              That's the 10.2 manual.  And it doesn't show the initialization function.  I don't think the example helps the discussion.

               

              More interestingly, is whether .NET will change, or have already changed.  Have you tried a recent version?  I suspect initialization is the kind of code they won't touch, but maybe they did.

              • 4. Re: Bug with OCI 12.1.0.2.0 and 12.2.0.1.0.
                Alex Keh - Product Manager-Oracle

                Hi Dmitriy,

                System.Data.OracleClient is Microsoft's .NET data provider for Oracle DB. It was deprecated in 2009. That is likely why you are hitting this bug as the data provider code has not been updated for the latest Oracle DB releases.

                 

                You can try using ODP.NET, which is Oracle's own ADO.NET provider. However, ODP.NET has not added an SSRS data processing extension. Another couple of alternatives is to use OLE DB or ODBC.

                • 5. Re: Bug with OCI 12.1.0.2.0 and 12.2.0.1.0.
                  cj

                  Another snippet from our .NET team is "ODP.NET, unmanaged driver (which relies on OCI), uses OCIEnvNlsCreate (and not OCIEnvCreate)."