Looking for ways to fetch large (> 1MB) long raw data using OCI

[Jim Monte]

Hi,

I am having difficulty fetching large BLOB (long raw) of unknown size
from an Oracle 8.1.5 database using OCI. Wondering if there is a
recommended method of doing this. Any success stories, sample codes,
pointers will be invaluable in getting me out of this mess.

Thanks in advance.

Jim

[stuart coupe]

Jim Monte wrote:

I have been using this on 816/817. Hope it is of use.

Cheers,
Stuart.

====================================================================================================

#include <oci.h>
#include <ocidfn.h>
#include <stdio.h>
#include <stdlib.h>

#define DATA_SIZE 500000

/*----------------------------------------------------------------------------*/

void error( sword ret, OCIError* oci_err, char* func , int lineno )
{
text msgbuf[100];
sb4 errcode = 0;

fprintf(stdout,"ORACLE error in %s at %d\n", func, lineno ) ;
switch ( ret )
{
case OCI_INVALID_HANDLE :
printf ( "Invalid handle\n" ) ;
break ;
case OCI_NEED_DATA :
printf ( "Needed Data.\n" ) ;
return ;
default :
if ( ! OCIErrorGet ((dvoid *) oci_err, (ub4) 1, (text *) NULL,
&errcode,
msgbuf, (ub4) sizeof(msgbuf), (ub4)
OCI_HTYPE_ERROR))
{
fprintf(stdout,"ERROR CODE = %d\n", errcode);
fprintf(stdout,"%s\n", msgbuf);
}
}
exit(1);
}

/*----------------------------------------------------------------------------*/int
main ( int argc, char* argv[] )
{
int i = 0 ;
sword ret ;
sb2 ind ;
OCIError* oci_err ;
OCIEnv* oci_env ;
OCISvcCtx* oci_svc ;
OCIStmt* oci_stmt ;
char data [ DATA_SIZE ] ;
OCIDefine* oci_define ;
OCIParam* param ;
ub4 num_cols ;
char file_name [ 100 ] ;
FILE* fp ;

char *DB ="oracle.elf" ;
char* UID ="system" ;
char* PWD ="manager" ;

char* sql = argv[1] ;

OCIInitialize( OCI_DEFAULT, 0, 0, 0, 0 );

OCIEnvInit( &oci_env, OCI_DEFAULT, 0, 0 );

ret=OCIHandleAlloc( oci_env, (dvoid**)&oci_err, OCI_HTYPE_ERROR, 0, 0
);
if (ret) error ( ret, oci_err, "OCIHandleAlloc", __LINE__ ) ;

ret=OCILogon( oci_env,oci_err,&oci_svc,
UID,strlen(UID), PWD,strlen(PWD), DB, strlen(DB) ) ;
if (ret) error ( ret, oci_err, "OCILogon", __LINE__ ) ;

ret=OCIHandleAlloc( oci_env, (dvoid**)&oci_stmt, OCI_HTYPE_STMT, 0, 0
);
if (ret) error ( ret, oci_err, "OCIHandleAlloc", __LINE__ ) ;

printf ( "\nSQL: %s\n", sql ) ;
ret=OCIStmtPrepare( oci_stmt, oci_err, sql,
strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT) ;
if (ret) error ( ret, oci_err, "OCIStmtPrepare", __LINE__ ) ;

ret=OCIDefineByPos( oci_stmt, &oci_define, oci_err, 1, 0, (sb4)
SB4MAXVAL,
SQLT_LNG,(dvoid *)NULL, (ub2*)NULL, (ub2*)0, (ub4)
OCI_DYNAMIC_FETCH) ;
if (ret) error ( ret, oci_err, "OCIDefineByPos", __LINE__ ) ;

ret =
OCIStmtExecute(oci_svc,oci_stmt,oci_err,0,0,0,0,OCI_COMMIT_ON_SUCCESS);
if ( ret == -1 ) error ( ret, oci_err, "OCIStmtExecute", __LINE__ ) ;

while ( 1 )
{
int total_size = 0 ;
ub4 data_length = 1000 ;
ret = OCIStmtFetch( oci_stmt, oci_err, 1, OCI_FETCH_NEXT,
OCI_DEFAULT);
if ( ret && ( ret != OCI_NEED_DATA) )
{
error ( ret, oci_err, "OCIStmtFetch", __LINE__ ) ;
break ;
}
do
{

sb2 ind ;
ub2 return_code ;
ub1 piece , in_outp ;
ub4 type, iterp, idxp ;

ret = OCIStmtGetPieceInfo(oci_stmt, oci_err, &oci_define,
&type, &in_outp, &iterp, &idxp, &piece );
if (ret ) error ( ret, oci_err, "OCIStmtGetPieceInfo", __LINE__ )
;
ret = OCIStmtSetPieceInfo ( oci_define, OCI_HTYPE_DEFINE, oci_err,

((char*)data)+total_size, &data_length,piece, NULL,
NULL);
if (ret ) error ( ret, oci_err, "OCIStmtSetPieceInfo", __LINE__ )
;
ret = OCIStmtFetch( oci_stmt, oci_err, 1, OCI_FETCH_NEXT,
OCI_DEFAULT);
if (ret && ( ret != OCI_NEED_DATA ) )
error ( ret, oci_err, "OCIStmtFetch", __LINE__ ) ;

total_size += data_length ;

} while ( ret == OCI_SUCCESS_WITH_INFO || ret == OCI_NEED_DATA);
printf ( "\n\n Total Size %d\n\n", total_size ) ;
sprintf ( file_name, "%d.out", i++ ) ;
fp = fopen( file_name , "wb") ;
fwrite( data, 1, total_size , fp);
fclose ( fp ) ;

}

ret=OCIHandleFree( oci_stmt, OCI_HTYPE_STMT ) ;
if (ret) error ( ret, oci_err, "OCIHandleFree", __LINE__ ) ;

ret=OCILogoff( oci_svc, oci_err ) ;
if (ret) error ( ret, oci_err, "OCILogoff", __LINE__ ) ;

ret=OCIHandleFree( oci_err, OCI_HTYPE_ERROR ) ;
if (ret) error ( ret, oci_err, "OCIHandleFree", __LINE__ ) ;

ret=OCIHandleFree( oci_env, OCI_HTYPE_ENV ) ;
if (ret) error ( ret, oci_err, "OCIHandleFree", __LINE__ ) ;

printf ( "\nFinished\n" ) ;

}

[Jim Monte]

Thanks for the code. It looks very similar to mine. The important
difference is in the SB4MAXVAL in the function call below. The
OCI documentation I read claims that it is ignored for dynamic fetches,
but changing it to 0 gives an "ORA-03106: fatal two-task communication
protocol error" when fetching the first piece.

> OCIDefineByPos( oci_stmt, &oci_define, oci_err, 1, 0, (sb4)
> SB4MAXVAL, SQLT_LNG,(dvoid *)NULL, (ub2*)NULL, (ub2*)0, (ub4)
> OCI_DYNAMIC_FETCH) ;

I hope this helps some else trying to figure out what caused an ORA-03106
error when doing a piecewise fetch ...

Jim

stuart coupe <stu...@easysoft.com> wrote in message news:<3BB1EB26...@easysoft.com>...