This discussion is archived
7 Replies Latest reply: Mar 13, 2013 2:29 AM by 951580 RSS

Help Getting Simple ODCI program to work.

557564 Newbie
Currently Being Moderated
Hello. I am trying to understand a pipelined table function / cartridge example given in the oracle docs:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dcitblfnxemp.htm#sthref859


But, I can't seem to get my example to work. I basically made some modifications so that the pipelined table will read from a function, the values of Books. I never get to the point where it can actually do a read. The code is below, but let me explain what I see.

Here's what appears to be my problem: the value of self in ODCITableStart is different than thevalue of self passed to ODCITableFetch. I don't know if that could be the problem, but is this normal?

So what happens is in ODCITableStart, a key is generated. And I can see the value being 1. This is assigned to self->key.

Later, in ODCITableFetch, we try to get the key back. We call GetStoredCtx(), which tries to retrieve the key from self->key. This should be a pointer, but what gets returned iis a null value. Then, ofcourse, the whole thing fails.

Anyways, what am I doing wrong here?


thanks.



book.sql
--

-- Create the types for the table function's output collection
-- and collection elements


CREATE OR REPLACE TYPE BookType AS OBJECT
(
lastname VARCHAR(30),
firstname VARCHAR(30),
title VARCHAR(30)
);
/

CREATE OR REPLACE TYPE BookTypeSet AS TABLE OF BookType;
/

-- Create the external library object

CREATE OR REPLACE LIBRARY BookLib IS '/home/me/playcode/book.so';
/

-- Create the implementation type

CREATE OR REPLACE TYPE BookImpl AS OBJECT
(
key RAW(4),

STATIC FUNCTION ODCITableStart(sctx IN OUT BookImpl, filename in varchar2)
RETURN PLS_INTEGER
AS LANGUAGE C
LIBRARY BookLib
NAME "ODCITableStart"
WITH CONTEXT
PARAMETERS (
context,
sctx,
sctx INDICATOR STRUCT,
filename,
RETURN INT
),

MEMBER FUNCTION ODCITableFetch(self IN OUT BookImpl, nrows IN NUMBER,
outSet OUT BookTypeSet) RETURN PLS_INTEGER
AS LANGUAGE C
LIBRARY BookLib
NAME "ODCITableFetch"
WITH CONTEXT
PARAMETERS (
context,
self,
self INDICATOR STRUCT,
nrows,
outSet,
outSet INDICATOR,
RETURN INT
),

MEMBER FUNCTION ODCITableClose(self IN BookImpl) RETURN PLS_INTEGER
AS LANGUAGE C
LIBRARY BookLib
NAME "ODCITableClose"
WITH CONTEXT
PARAMETERS (
context,
self,
self INDICATOR STRUCT,
RETURN INT
)

);
/


-- Create table function

CREATE OR REPLACE FUNCTION BookFunc( lastname in varchar2) RETURN BookTypeSet
PIPELINED USING BookImpl;
/


-- Cleanup
drop type BookImpl;
drop function BookFunc;
drop type BookTypeSet;
drop type BookType;



--


book.c:

#include <assert.h>
#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdarg.h>
#include <signal.h>
#include <errno.h>
#include <time.h>

#ifndef OCI_ORACLE
# include <oci.h>
#endif
#ifndef ODCI_ORACLE
# include <odci.h>
#endif

/*---------------------------------------------------------------------------
PRIVATE TYPES AND CONSTANTS
---------------------------------------------------------------------------*/

/* The struct holding the user's stored context */

struct StoredCtx
{
OCIStmt* stmthp;
FILE *fio;
char filename[50];
};
typedef struct StoredCtx StoredCtx;

/* OCI Handles */

struct Handles_t
{
OCIExtProcContext* extProcCtx;
OCIEnv* envhp;
OCISvcCtx* svchp;
OCIError* errhp;
OCISession* usrhp;
};
typedef struct Handles_t Handles_t;

/********************** SQL Types C representation **********************/

/* Table function's implementation type */


struct BookImpl
{
//short _atomic;
OCIRaw *key;
char *filename;
};
typedef struct BookImpl BookImpl;

struct BookImpl_ind
{
//short atomic;
short key;
short filename;
};
typedef struct BookImpl_ind BookImpl_ind;


/* Table function's output collection element type */

struct BookType
{
OCIString* lastname;
OCIString* firstname;
OCIString* title;
};
typedef struct BookType BookType;

struct BookType_ind
{
//short _atomic;
short lastname;
short firstname;
short title;
};
typedef struct BookType_ind BookType_ind;

/* Table function's output collection type */

typedef OCITable BookTypeSet;

/*--------------------------------------------------------------------------*/
/* Static Functions */
/*--------------------------------------------------------------------------*/

static int GetHandles(OCIExtProcContext* extProcCtx, Handles_t* handles);

static StoredCtx* GetStoredCtx(Handles_t* handles, BookImpl* self,
BookImpl_ind* self_ind);

static int checkerr(Handles_t* handles, sword status);

/*--------------------------------------------------------------------------*/
/* Functions definitions */
/*--------------------------------------------------------------------------*/

/* Callout for ODCITableStart */

int ODCITableStart(OCIExtProcContext* extProcCtx, BookImpl* self,
BookImpl_ind* self_ind, char *filename)
{
Handles_t handles; /* OCI hanldes */
StoredCtx* storedCtx; /* Stored context pointer */

ub4 key; /* key to retrieve stored context */

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

/* Allocate memory to hold the stored context */
if (checkerr(&handles, OCIMemoryAlloc((dvoid*) handles.usrhp, handles.errhp,
(dvoid**) &storedCtx,
OCI_DURATION_STATEMENT,
(ub4) sizeof(StoredCtx),
OCI_MEMORY_CLEARED)))
return ODCI_ERROR;

/* store the input ref cursor in the stored context */
//storedCtx->stmthp=*cur;

strcpy( storedCtx->filename, filename);
storedCtx->fio = fopen( storedCtx->filename, "r");

/* generate a key */
if (checkerr(&handles, OCIContextGenerateKey((dvoid*) handles.usrhp,
handles.errhp, &key)))
return ODCI_ERROR;

/* associate the key value with the stored context address */
if (checkerr(&handles, OCIContextSetValue((dvoid*)handles.usrhp,
handles.errhp,
OCI_DURATION_STATEMENT,
(ub1*) &key, (ub1) sizeof(key),
(dvoid*) storedCtx)))
return ODCI_ERROR;


/* stored the key in the scan context */
if (checkerr(&handles, OCIRawAssignBytes(handles.envhp, handles.errhp,
(ub1*) &key, (ub4) sizeof(key),
&(self->key))))
return ODCI_ERROR;

/* set indicators of the scan context */
// self_ind->atomic = OCIIND_NOTNULL;
self_ind->key = OCI_IND_NOTNULL;


// For debugging
ub4 keylen; /* length of key */
keylen = OCIRawSize(handles.envhp, self->key);



return ODCI_SUCCESS;
}

/***********************************************************************/

/* Callout for ODCITableFetch */

int ODCITableFetch(OCIExtProcContext* extProcCtx, BookImpl* self,
BookImpl_ind* self_ind, OCINumber* nrows,
BookTypeSet** outSet, short* outSet_ind)
{
Handles_t handles; /* OCI hanldes */
StoredCtx* storedCtx; /* Stored context pointer */
int nrowsval; /* number of rows to return */
char readbuffer[1024];
char *fval = NULL;


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

/* Get the stored context */
storedCtx=GetStoredCtx(&handles,self,self_ind);
if (!storedCtx) return ODCI_ERROR;

/* get value of nrows */
if (checkerr(&handles, OCINumberToInt(handles.errhp, nrows, sizeof(nrowsval),
OCI_NUMBER_SIGNED, (dvoid *)&nrowsval)))
return ODCI_ERROR;

/* return up to 10 rows at a time */
if (nrowsval>10) nrowsval=10;

/* Initially set the output to null */
*outSet_ind=OCI_IND_NULL;

while (nrowsval>0)
{

BookType elem; /* current collection element */
BookType_ind elem_ind; /* current element indicator */

#if 0
OCIDefine* defnp1=(OCIDefine*)0; /* define handle */
OCIDefine* defnp2=(OCIDefine*)0; /* define handle */
OCIDefine* defnp3=(OCIDefine*)0; /* define handle */
#endif

char lastname[30];
char firstname[30];
char title[30];

#if 0
char ticker[5];
float openprice;
float closeprice;
char PriceType[2];

/* Define the fetch buffer for ticker symbol */
if (checkerr(&handles, OCIDefineByPos(storedCtx->stmthp, &defnp1,
handles.errhp, (ub4) 1,
(dvoid*) &lastname,
(sb4) sizeof(lastname),
SQLT_STR, (dvoid*) 0, (ub2*) 0,
(ub2*) 0, (ub4) OCI_DEFAULT)))
return ODCI_ERROR;


if (checkerr(&handles, OCIDefineByPos(storedCtx->stmthp, &defnp1,
handles.errhp, (ub4) 1,
(dvoid*) &firstname,
(sb4) sizeof(firstname),
SQLT_STR, (dvoid*) 0, (ub2*) 0,
(ub2*) 0, (ub4) OCI_DEFAULT)))
return ODCI_ERROR;


if (checkerr(&handles, OCIDefineByPos(storedCtx->stmthp, &defnp1,
handles.errhp, (ub4) 1,
(dvoid*) &title,
(sb4) sizeof(title),
SQLT_STR, (dvoid*) 0, (ub2*) 0,
(ub2*) 0, (ub4) OCI_DEFAULT)))
return ODCI_ERROR;


/* fetch a row from the input ref cursor */
status = OCIStmtFetch(storedCtx->stmthp, handles.errhp, (ub4) 1,
(ub4) OCI_FETCH_NEXT, (ub4) OCI_DEFAULT);

/* finished if no more data */
if (status!=OCI_SUCCESS && status!=OCI_SUCCESS_WITH_INFO) break;
#endif

/* Initialize the element indicator struct */

// elem_ind._atomic=OCI_IND_NOTNULL;
elem_ind.lastname=OCI_IND_NOTNULL;
elem_ind.firstname=OCI_IND_NOTNULL;
elem_ind.title=OCI_IND_NOTNULL;

// Read from file.
{
char field1,field2,*field3;
char comma1, comma2;

fval = fgets( readbuffer, sizeof( readbuffer), storedCtx->fio);
if( fval == NULL)
break;

readbuffer[strlen(readbuffer)] = '\0'; // Chop newline.

comma1 = strchr( readbuffer, ',');
comma2 = strchr( comma1+1, ',');

*comma1 = '\0';
*comma2 = '\0';

strcpy( lastname, readbuffer);
strcpy( firstname, comma1+1);
strcpy( title, comma2+1);


#if 0
field1 = readbuffer;
field2 = strchr(field1+1, ',')+1; // +1 to move past the ','
field3 = strchr(field2+1, ',')+1;

memset( lastname, 0, sizeof(lastname));
memset( firstname, 0, sizeof( firstname));
memset( title, 0, sizeof(title));

strncpy( lastname , field1, field2 - field1 - 1);
strncpy( firstname, field2, field3 - field2 - 1);
strncpy( title, field3, strlen(readbuffer) - field2 - 1);
#endif
} // Reading from file.

/* assign the ticker name */
elem.lastname=NULL;
if (checkerr(&handles, OCIStringAssignText(handles.envhp, handles.errhp,
(text*) lastname,
(ub2) strlen(lastname),
&elem.lastname)))
return ODCI_ERROR;


/* assign the ticker name */
elem.firstname=NULL;
if (checkerr(&handles, OCIStringAssignText(handles.envhp, handles.errhp,
(text*) firstname,
(ub2) strlen(firstname),
&elem.firstname)))
return ODCI_ERROR;
/* assign the ticker name */
elem.title=NULL;
if (checkerr(&handles, OCIStringAssignText(handles.envhp, handles.errhp,
(text*) title,
(ub2) strlen(title),
&elem.title)))
return ODCI_ERROR;

#if 0
/* assign the price */
if (checkerr(&handles, OCINumberFromReal(handles.errhp, &openprice,
sizeof(openprice), &elem.price)))
return ODCI_ERROR;
#endif

/* append element to output collection */
if (checkerr(&handles, OCICollAppend(handles.envhp, handles.errhp,
&elem, &elem_ind, *outSet)))
return ODCI_ERROR;

/* set collection indicator to not null */
*outSet_ind=OCI_IND_NOTNULL;

nrowsval-=1;
}

return ODCI_SUCCESS;
}

/***********************************************************************/

/* Callout for ODCITableClose */

int ODCITableClose(OCIExtProcContext* extProcCtx, BookImpl* self,
BookImpl_ind* self_ind)
{
Handles_t handles; /* OCI hanldes */
StoredCtx* storedCtx; /* Stored context pointer */

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

/* Get the stored context */
storedCtx=GetStoredCtx(&handles,self,self_ind);
if (!storedCtx) return ODCI_ERROR;

fclose( storedCtx->fio);

/* Free the memory for the stored context */
if (checkerr(&handles, OCIMemoryFree((dvoid*) handles.usrhp, handles.errhp,
(dvoid*) storedCtx)))
return ODCI_ERROR;

return ODCI_SUCCESS;
}

/***********************************************************************/

/* Get the stored context using the key in the scan context */

static StoredCtx* GetStoredCtx(Handles_t* handles, BookImpl* self,
BookImpl_ind* self_ind)
{
StoredCtx storedCtx;           / Stored context pointer */
ub1 key;                       / key to retrieve context */
ub4 keylen; /* length of key */

/* return NULL if the PL/SQL context is NULL */
// if (self_ind->atomic == OCIIND_NULL) return NULL;

/* Get the key */
key = OCIRawPtr(handles->envhp, self->key);
keylen = OCIRawSize(handles->envhp, self->key);

/* Retrieve stored context using the key */
if (checkerr(handles, OCIContextGetValue((dvoid*) handles->usrhp,
handles->errhp,
key, (ub1) keylen,
(dvoid**) &storedCtx)))
return NULL;

return storedCtx;
}

/***********************************************************************/

/* Get OCI handles using the ext-proc context */

static int GetHandles(OCIExtProcContext* extProcCtx, Handles_t* handles)
{
/* store the ext-proc context in the handles struct */
handles->extProcCtx=extProcCtx;

/* Get OCI handles */
if (checkerr(handles, OCIExtProcGetEnv(extProcCtx, &handles->envhp,
&handles->svchp, &handles->errhp)))
return -1;

/* get the user handle */
if (checkerr(handles, OCIAttrGet((dvoid*)handles->svchp,
(ub4)OCI_HTYPE_SVCCTX,
(dvoid*)&handles->usrhp,
(ub4*) 0, (ub4)OCI_ATTR_SESSION,
handles->errhp)))
return -1;

return 0;
}

/***********************************************************************/

/* Check the error status and throw exception if necessary */

static int checkerr(Handles_t* handles, sword status)
{
text errbuf[512]; /* error message buffer */
sb4 errcode; /* OCI error code */

switch (status)
{
case OCI_SUCCESS:
case OCI_SUCCESS_WITH_INFO:
return 0;
case OCI_ERROR:
OCIErrorGet ((dvoid*) handles->errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
sprintf((char*)errbuf, "OCI ERROR code %d",errcode);
break;
default:
sprintf((char*)errbuf, "Warning - error status %d",status);
break;
}

OCIExtProcRaiseExcpWithMsg(handles->extProcCtx, 29400, errbuf,
strlen((char*)errbuf));

return -1;
}
  • 1. Re: Help Getting Simple ODCI program to work.
    654443 Newbie
    Currently Being Moderated
    Hi,

    Can you post your Make file or the gcc and ld commands which you are using to compile your program.
  • 2. Re: Help Getting Simple ODCI program to work.
    557564 Newbie
    Currently Being Moderated
    Thanks for perusing the code.

    Here's what I have for a makefile. I had to change the prefix and install_sh paths to hide my name, and other private info. Also, there's a mycomp* references. This is another module that I have to play around with ODCI. You can take that out.


    CC=gcc -pipe -m32
    CXX=g++ -pipe -m32
    svn_root=../../..
    prefix=/home/me/stage
    exec_prefix=${prefix}
    install_sh=/home/me/install-sh
    LIB=${exec_prefix}/lib
    INC=-I${prefix}/include -I$(ORACLE_HOME)/rdbms/public
    CFLAGS=-g -O0
    override CFLAGS := -Wall -std=c99 -fno-strict-aliasing $(CFLAGS) $(INC)

    all:
         $(CC) $(CFLAGS) -c -fPIC book.c
         $(CC) $(CFLAGS) -c -fPIC mycomp_exec.c
         $(CC) $(CFLAGS) -shared -fPIC -o book.so book.o -L$(LIB)
         $(CC) $(CFLAGS) -shared -fPIC -o mycomp_exec.so mycomp_exec.o -L$(LIB) -ldtpq

    install: mycomp_exec.so
         ${install_sh} -d $(LIB)/ora
         ${install_sh} -t $(LIB)/ora book.so
         ${install_sh} -t $(LIB)/ora mycomp_exec.so

    clean:
         rm -rf book.o book.so
         rm -rf mycomp_exec.o mycomp_exec.so

    .PHONY: install all clean

    --

    Anyways, I think the problem is, I don't create the type in ODCITableStart. So, I think Oracle instantiates a BookImpl twice; once for the start function, and another for the fetch function.

    What I think I will do is create an PL/SQL version ODCITableStart program that will instantiate a a BookImplType like so:

    self := BookImpl( filename );

    then calls the C function above which will be renamed to somethine like _TableStart.

    thanks.
  • 3. Re: Help Getting Simple ODCI program to work.
    654443 Newbie
    Currently Being Moderated
    can you list here all the files in the following directories:

    ${exec_prefix}/lib

    ${prefix}/include

    $(ORACLE_HOME)/rdbms/public


    what is the use of this -ldtpq library for ?

    ${install_sh} -t $(LIB)/ora book.so : Is this command for copying the book.so file into that directory ?

    What are you trying to install using install-sh ?
  • 4. Re: Help Getting Simple ODCI program to work.
    557564 Newbie
    Currently Being Moderated
    Hello.

    The make file compiles the modules then copies it into target directory. but, the create library command lists the source directory, not the target directory. so, I don't think it matters what install-sh does? Anyways, the dtpq library is ours, but no functions are called from it. So it can be removed.

    Anyways, I tried to create a plsql function:

    CREATE OR REPLACE TYPE BODY BookImpl
    AS
    STATIC FUNCTION ODCITableStart(sctx IN OUT BookImpl, filename in varchar2)
    RETURN NUMBER
    IS
    fookey RAW(4);
    rval number;
    BEGIN
    sctx := BookImpl( fookey, 'start' );
    rval := tablestart( sctx, filename );
    RETURN odciconst.success;
    END;
    END;
    /

    tablestart is now the C function listed above. the 'start' is a new argument I added for debugging. Hmm.. it still doesn't work.

    Anyways, you got any ideas? I'll post up some gdb stacks.
  • 5. Re: Help Getting Simple ODCI program to work.
    654443 Newbie
    Currently Being Moderated
    Hi,

    I am also having similar situation like you but with different part of ODCI. I am trying to use ODCIAggregate interface and was getting an error:

    ORA-28579: network error during callback from external procedure agent

    So I thought may be I will look into your code and see if I can find some thing which might be missing related to ODCI service and then try solving it together. But I could not find any clue till now.

    May be one thing I will do is try running your code on our server machine on saturday and see if I can get any better results or not and then check the messages being generated and try figuring out if possible.

    If you have further information on this, keep posting messages here.
  • 6. Re: Help Getting Simple ODCI program to work.
    654443 Newbie
    Currently Being Moderated
    Hey,

    Are you able to run your program now.
  • 7. Re: Help Getting Simple ODCI program to work.
    951580 Newbie
    Currently Being Moderated
    Hi,

    Actually I found the example does not work at all. I didn't change any thing, but compile, copy and run..

    There is a Data Cartridge error reported at ODCITableFetch, the "cur" passed into ODCITableStart becomes invalid at first OCIDefineByPos call.

    Tested with 11gr2 XE edition win32 / linux64.

    p.s. the lib is successfully loaded and I can even debug the code until OCIDefineByPos.

    Clark

    Edited by: 948577 on Mar 13, 2013 5:29 PM