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

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 <stuart_at_easysoft.com> wrote in message news:<3BB1EB26.96C6CDF1_at_easysoft.com>...
> Jim Monte wrote:
>
> > 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
>
> 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" ) ;
>
> }
Received on Mon Oct 08 2001 - 08:52:56 CDT