This discussion is archived
4 Replies Latest reply: Jan 19, 2012 3:31 AM by 894085 RSS

Newbie Question on OCIObjectNew()

907297 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    If it's any comfort, I get the exact same internal error.

    Still playing...
  • 3. Re: Newbie Question on OCIObjectNew()
    423410 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points