Home » Developer & Programmer » Precompilers, OCI & OCCI » ODCITable Interface using extproc using OCIType help
ODCITable Interface using extproc using OCIType help [message #150230] Tue, 06 December 2005 17:39
jesuchin
Messages: 1
Registered: December 2005
Junior Member
I am trying to implement the OCITable functions:
-ODCITableDescribe
-ODCITablePrepare
-ODCITableStart
-ODCITableFetch
-ODCITableClose
in a c dll. The problem I am having is that I get an exception in my ODCITableDescribe function. I want the Pipelined procedure to return a table from which the c dll has read from an input csv file. The problem is that csv file can have any number of columns. So This is why I am using ODCITableDescribe method to tell pl/sql pipelined procedure at compile time what my table is going to look like.

Here is my pl/sql code snippet for ODCITableDescribe:

STATIC FUNCTION ODCITableDescribe(typ1 OUT SYS.ANYTYPE, sfile VARCHAR2) 
RETURN PLS_INTEGER
AS LANGUAGE C
LIBRARY StrTabLib
NAME "ODCITableDescribe"
WITH CONTEXT
PARAMETERS (	context,
		typ1,
		typ1 INDICATOR,
		sfile OCIString,
		RETURN INT),	


And finally my code for c implementation of ODCITableDescribe:

int STRTAB_API ODCITableDescribe(OCIExtProcContext* extProcCtx, 
				 OCIType*	    typ1,
				 OCIInd*	    typ1_ind,
                                 char* 		    sfile)
{
    Handles_t handles;	// OCI hanldes
	
    OCIParam* vchar_param  = (OCIParam*) 0;
    OCIParam* tab_param	   = (OCIParam*) 0;

    OCIType* obj_tdo = (OCIType*) 0;
    OCIType* tab_tdo = (OCIType*) 0;

    OCITypeCode tc = OCI_TYPECODE_VARCHAR2;

    ub2 len = 4000;
    ub2 csid = OCI_UCS2ID;
    ub1 csfrm = SQLCS_NCHAR;
    sword status = ODCI_ERROR;

    try
    {
       // Get OCI handles 
       if (GetHandles(extProcCtx, &handles))	
	 return ODCI_ERROR;

	*typ1_ind = OCI_IND_NULL;

	// create object	
	status = OCITypeBeginCreate(	handles.svchp, 
					handles.errhp, 
					OCI_TYPECODE_OBJECT, 
					OCI_DURATION_SESSION, 
					&obj_tdo);

        // Start filling info for varchar2 type
	status = OCIDescriptorAlloc(	(dvoid *)handles.envhp, 
					(dvoid **)&vchar_param,
					(ub4)OCI_DTYPE_PARAM, 
					0, 
					(dvoid **)0);

	status = OCIAttrSet	(	(dvoid *)vchar_param, 
					(ub4)OCI_DTYPE_PARAM, 
					(dvoid *)&tc,
					(ub4) sizeof(ub2), 
					(ub4)OCI_ATTR_TYPECODE, 
					handles.errhp);

	status = OCIAttrSet	(	(dvoid *)vchar_param, 
					(ub4)OCI_DTYPE_PARAM, 
					(dvoid *)&len,
					(ub4) sizeof(ub4), 
					(ub4)OCI_ATTR_DATA_SIZE, 
					handles.errhp);

	status = OCIAttrSet	(	(dvoid *)vchar_param, 
					(ub4)OCI_DTYPE_PARAM, 
					(dvoid *)&csid,
					(ub4) sizeof(ub2), 
					(ub4)OCI_ATTR_CHARSET_ID, 
					handles.errhp);

	status = OCIAttrSet	(	(dvoid *)vchar_param, 
					(ub4)OCI_DTYPE_PARAM, 
					(dvoid *)&csfrm,
					(ub4) sizeof(ub1), 
                                        (ub4)OCI_ATTR_CHARSET_FORM, 
					handles.errhp);

	 //Add varchar2 column to object
        status = OCITypeAddAttr	(	handles.svchp, 
					handles.errhp,
                                        obj_tdo, 
					(text *)"attr1",
					(ub4)strlen("attr1"),
					vchar_param);

	status = OCITypeEndCreate(	handles.svchp, 
					handles.errhp, 
					obj_tdo);

	 
		
       //create table as objects of varchar2s
	status = OCITypeBeginCreate(	handles.svchp, 
					handles.errhp, 
					OCI_TYPECODE_TABLE, 
					OCI_DURATION_SESSION, 
					&tab_tdo);

	status = OCIDescriptorAlloc(	(dvoid *)handles.envhp, 
					(dvoid **)&tab_param,
					(ub4)OCI_DTYPE_PARAM, 
					0, 
					(dvoid **)0);

	tc = OCI_TYPECODE_OBJECT;
	status = OCIAttrSet	(	(dvoid *)tab_param, 
					(ub4)OCI_DTYPE_PARAM, 
					(dvoid *)&tc,
					(ub4) sizeof(ub2), 
					(ub4)OCI_ATTR_TYPECODE, 
					handles.errhp);

	status = OCIAttrSet	(	(dvoid *)tab_param, 
					(ub4)OCI_DTYPE_PARAM, 
					(dvoid *)obj_tdo,
					(ub4) sizeof(OCIType *), 
					(ub4)OCI_ATTR_TDO, 
					handles.errhp);

	status = OCITypeSetCollection(  handles.svchp,
					handles.errhp,
					tab_tdo,
					tab_param,
					0);

         // The NEXT STATEMENT THROWS EXCEPTION
	status = OCITypeEndCreate(	handles.svchp, 
					handles.errhp, 
					tab_tdo);

	typ1 = tab_tdo;	
	*typ1_ind = OCI_IND_NOTNULL;

    }
    catch(...)
    {
      return ODCI_ERROR;
    }

    return ODCI_SUCCESS;
}


Currently this function should only create a transient type table of 1 column. But it throws on the last OCITypeEndCreate. I have tested this function using exec setup (program with main) and it works there however when accessing this function through pl/sql's extproc as a dll it has problems.
Additionally, through debugging I have checked all the return status from the OCI calls and they are all OCI_SUCCESS.

Any help or tips would be greatly appreciated as I have spent hours going through the documentation(tons of pdfs from oracle).

Thanks for any comments!
Previous Topic: Information About proc*
Next Topic: PHPoci and ORA-03113: end-of-file on communication channel
Goto Forum:
  


Current Time: Mon Oct 26 23:10:00 CDT 2020