4 Replies Latest reply: Jan 19, 2012 5:31 AM by 894085 RSS

    Newbie Question on OCIObjectNew()

    907297
      Is is allowed to use OCIObjectNew to create an instance of a user-defined Varray type?

      I just use OCI to create a Varray(100) of OCINumber. When I use OCIObjectNew() to initialize an instance for this type. I just get an error

      OCI-21500: Internal Error Code: [kohaii039], [], [], [], [], [], [], []

      My version of Oracle and C is (Oracle 10.2.0 VC++6.0)

      Here is my c file:
      #include <stdio.h>
      #include <cstring>
      #include <oci.h >
      #include "Ref_Handles.h"

      int initialize(RefHandles &handle,char* dbname,char* username,char* password)
      {
      sword status;
      /* initialize the mode to be the threaded and object environment */
      status= OCIEnvCreate(&handle.myenvhp, OCI_OBJECT, (dvoid *)0,
      0, 0, 0, (size_t) 0, (dvoid **)0);

      /* allocate a server handle */
      status= OCIHandleAlloc ((dvoid *)handle.myenvhp, (dvoid **)&handle.mysrvhp,
      OCI_HTYPE_SERVER, 0, (dvoid **) 0);

      /* allocate an error handle */
      status= OCIHandleAlloc ((dvoid *)handle.myenvhp, (dvoid **)&handle.myerrhp,
      OCI_HTYPE_ERROR, 0, (dvoid **) 0);

      /* create a server context */
      status= OCIServerAttach (handle.mysrvhp, handle.myerrhp, (text *)dbname,
      strlen(dbname), OCI_DEFAULT);

      /* allocate a service handle */
      status= OCIHandleAlloc ((dvoid *)handle.myenvhp, (dvoid **)&handle.mysvchp,
      OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);

      /* set the server attribute in the service context handle*/
      status= OCIAttrSet ((dvoid *)handle.mysvchp, OCI_HTYPE_SVCCTX,
      (dvoid *)handle.mysrvhp, (ub4) 0, OCI_ATTR_SERVER, handle.myerrhp);

      /* allocate a user session handle */
      status= OCIHandleAlloc ((dvoid *)handle.myenvhp, (dvoid **)&handle.myusrhp,
      OCI_HTYPE_SESSION, 0, (dvoid **) 0);

      /* set username attribute in user session handle */
      status= OCIAttrSet ((dvoid *)handle.myusrhp, OCI_HTYPE_SESSION,
      (dvoid *)username, (ub4)strlen(username),
      OCI_ATTR_USERNAME, handle.myerrhp);

      /* set password attribute in user session handle */
      status= OCIAttrSet ((dvoid *)handle.myusrhp, OCI_HTYPE_SESSION,
      (dvoid *)password, (ub4)strlen(password),
      OCI_ATTR_PASSWORD, handle.myerrhp);

      status= OCISessionBegin ((OCISvcCtx *) handle.mysvchp, handle.myerrhp, handle.myusrhp,
      OCI_CRED_RDBMS, OCI_DEFAULT);

      /* set the user session attribute in the service context handle*/
      status= OCIAttrSet ( (dvoid *)handle.mysvchp, OCI_HTYPE_SVCCTX,
      (dvoid *)handle.myusrhp, (ub4) 0, OCI_ATTR_SESSION, handle.myerrhp);

      return status;

      }

      void main()
      {
      sword status;

      RefHandles handle;

      status=initialize(handle,"XE","juan","1234");

      if(status)
      {
      printf("Error Logon To Database.");
      return;
      }


      //define Collection type: varray(100) of OCINumber;
      OCIType * arr_num;
      OCIParam * v_param;
      OCITypeCode tc=OCI_TYPECODE_NUMBER;
      int scale=0,precision=5;

      status=OCITypeBeginCreate(handle.mysvchp,handle.myerrhp,OCI_TYPECODE_VARRAY,OCI_DURATION_SESSION,&arr_num);

      status=OCIDescriptorAlloc(handle.myenvhp,(void **)&v_param,OCI_DTYPE_PARAM,0,NULL);
      status=OCIAttrSet(v_param,OCI_DTYPE_PARAM,&tc,sizeof(OCITypeCode),OCI_ATTR_TYPECODE,handle.myerrhp);
      status=OCIAttrSet(v_param,OCI_DTYPE_PARAM,&scale,sizeof(int),OCI_ATTR_SCALE,handle.myerrhp);
      status=OCIAttrSet(v_param,OCI_DTYPE_PARAM,&precision,sizeof(int),OCI_ATTR_PRECISION,handle.myerrhp);

      status=OCITypeSetCollection(handle.mysvchp,handle.myerrhp,arr_num,v_param,100);//100 means maxlen of arr_num;

      status=OCITypeEndCreate(handle.mysvchp,handle.myerrhp,arr_num);


      printf("%d\n",status);// HERE I GET 0, which means success.


      //define a Collection Variable of the type defined above;
      OCIColl* v_test;
      status=OCIObjectNew(handle.myenvhp,handle.myerrhp,handle.mysvchp,OCI_TYPECODE_VARRAY,arr_num,NULL,OCI_DURATION_SESSION,TRUE,(void**)&v_test);



      printf("%d\n",status);//HERE I GET -1,
      sb4 errcode;
      text errbuf[500];
      OCIErrorGet ((dvoid *) handle.myerrhp, (ub4) 1, (text *) NULL, &errcode,
      errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);

      printf("%s\n",errbuf);//HERE: OCI-21500 Internal Error Code:[kohaii039][][][]...
      }

      Anything is wrong with my code?
        • 1. Re: Newbie Question on OCIObjectNew()
          907297
          If I use a datatype in the database instead of creating with OCITypeBeginCreate()...OCITypeEndCreate(), then the program runs smoothly without any error!

          Suppose user "Juan" has a type named ARR_NUM as:

          create type ARR_NUM as varray(100) of number;

          Modified c file:

          #include <stdio.h>
          #include <cstring>
          #include <oci.h >
          #include "Ref_Handles.h"

          int initialize(RefHandles &handle,char* dbname,char* username,char* password)
          {
          .....
          }

          void main()
          {
          sword status;

          RefHandles handle;

          status=initialize(handle,"XE","juan","1234");

          if(status)
          {
          printf("Error Logon To Database.");
          return;
          }

          //define Collection type from database;
          OCIType * arr_num;
          status=OCITypeByName(handle.myenvhp,
          handle.myerrhp,
          handle.mysvchp,
          (const text *)"",0,
          (const text *)"ARR_NUM",7,
          (const text *)"",0,
          OCI_DURATION_SESSION,
          OCI_TYPEGET_ALL,
          &arr_num);

          //define a Collection Variable of the type defined above;
          OCIColl* v_test;
          status=OCIObjectNew(handle.myenvhp,handle.myerrhp,handle.mysvchp,OCI_TYPECODE_VARRAY,arr_num,NULL,OCI_DURATION_SESSION,TRUE,(void**)&v_test);

          printf("%d\n",status); // Here I Get 0, which means SUCCESS.
          sb4 errcode;
          text errbuf[500];
          OCIErrorGet ((dvoid *) handle.myerrhp, (ub4) 1, (text *) NULL, &errcode,
          errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);

          printf("%s\n",errbuf);
          }

          Any explanation why we can't do the same thing on a user-defined type?
          • 2. Re: Newbie Question on OCIObjectNew()
            894085
            If it's any comfort, I get the exact same internal error.

            Still playing...
            • 3. Re: Newbie Question on OCIObjectNew()
              Sudheendra-Oracle
              OCIObjectNew can be used to instantiate a new Object.
              Once an object is instantiated you populate values into it and flush it back to the db.
              You can do this for Object Tables.
              ex: create type abc as object .....
              create table def of abc.

              If you want to insert values into a varray/nested table/user defined type which are created as columns of a table
              Then you need to use the following api's.
              Before that you need to get the type info generated into header files using the ott(Object Type Translator) tool
              This would translate a varray as follows in the header file it generates

              typedef OCIArray arr_num
              In the C file you need a pointer to this array
              arr_num *arr_var
              Then use OCINumberfromInt to create OCINumber's from Int
              use OCITypeName to populate arr_var using the OCINumber.

              You can read up the Objects Section in the OCI guide for some more clarity on its usage
              • 4. Re: Newbie Question on OCIObjectNew()
                894085
                The OP question is on a type created by OCITypeBeginCreate, rather than one created in the database. He has the database type working in his second post. It looks like VARRAY is not supported for client side dynamically created types.