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;
}