| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Insert 90MB data in one cell
A copy of this was sent to klimsamgin <klimsamgin_at_yahoo.com>
(if that email address didn't require changing)
On Tue, 27 Apr 1999 10:32:01 GMT, you wrote:
>Hi!
>
>I use LONG RAW column to store large binary datafiles in ORACLE 7.3
>under Solaris 2.5 (for example, 90MB file).
>And when I insert this big value in the table's cell
>at a heat, my PRO*C program can't allocate
>enough memory in the system (90MB contiguous memory buffer).
>To resolve this trouble, I should insert small
>pieces of this file in the cell(for examle, 1000x90KB, 1000 pieces).
>But how can I do this? Which command need used?
>
>Of couse, I can use 1000 cells, but what about one cell?
>
>Thanks,
>Klim.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
You might consider mixing OCI with your pro*c (see the sqllda function call in the OCI manual for more info). OCI (the Oracle Call Interface) can piecewise insert and fetch the LONG RAW data. Here is an example subroutine that loads a file in 64k chunks into my table:
void load_image( char * filename, char * mime_type, char * tname )
{
text longbuf[65536];
ub4 len_longbuf = sizeof(longbuf); ub4 piecesize = sizeof(longbuf);
printf("\nOpening source file %s\n", filename); if (!(input=fopen( filename, "rb" )) )
print_error_and_exit( "error opening file" );
fseek( input, 0, SEEK_END );
sprintf( img_size, "%ld", ftell( input ) );
fseek( input, 0, SEEK_SET );
printf( "File is %s bytes...\n", img_size );
sprintf( sqlstmt, "INSERT INTO %s ( name, mime_type, img_size, image ) \ VALUES (:name, :mime_type, :img_size, :image )", tname?tname:"image" );
if (oparse(&cda, sqlstmt, (sb4)-1, 0, (ub4)VERSION_7))
print_error_and_exit( oerr_cda() );
if (obndrv(&cda, (text *)":name", -1, filename, -1,
SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
print_error_and_exit( oerr_cda() );
if (obndrv(&cda, (text *)":mime_type", -1, mime_type, -1,
SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
print_error_and_exit( oerr_cda() );
if (obndrv(&cda, (text *)":img_size", -1, img_size, -1,
SQLT_STR, -1, (sb2 *)0, (ub1 *)0, -1, -1))
print_error_and_exit( oerr_cda() );
if (obindps(&cda, 0, (text *)":image",
strlen(":image"), (ub1 *)context, 999999999,
SQLT_LBI, (sword)0, (sb2 *)0,
(ub2 *)0, &col_rcode, 0, 0, 0, 0,
0, (ub4 *)0, (text *)0, 0, 0))
print_error_and_exit( oerr_cda() );
while (cont)
{
oexec(&cda);
printf( "return code %d\n", cda.rc );
switch (-cda.rc)
{
case 0: /* operation is finished */
cont = 0;
break;
case OCI_MORE_INSERT_PIECES: /* ORA-03129 was returned */
if ((len_longbuf = fread(longbuf, 1, len_longbuf, input)) == -1)
print_error_and_exit( "fread failed" );
if ( ogetpi(&cda, &piece, (dvoid**)&context,
&iteration,&plsqltable) )
print_error_and_exit( "ogetpi failed" );
if (len_longbuf < piecesize) /* last piece? */
{
piece = OCI_LAST_PIECE;
printf( "Setting piece to last piece..\n" );
}
if (osetpi(&cda, piece, longbuf, &len_longbuf))
print_error_and_exit( "osetpi failed" );
break;
default:
printf( "return code %d\n", cda.rc );
printf( oerr_cda() );
exit(1);
}
if (oclose(&cda)) /* close cursor */
print_error_and_exit( oerr_cda() ); }
Then, the OCI to read a long raw out might look like:
void save_images( char * sql_statement )
{
ub1 * ucp;
int UCP_SIZE = 65536;
sb2 indp = 0;
sb2 indp2 = 0;
ub4 ret_len = 0;
ub2 retl = 0;
ub2 retl2 = 0;
ub2 rcode = 0;
ub2 rcode2 = 0;
int fetched;
text file_name[255];
FILE * output;
int rc;
/* sqlstatement is a query that fetches a LONG RAW column
and a column that will be used as a FILENAME to store that
long raw into
print_error_and_exit("malloc failed" );
if (oparse(&cda, sql_statement, -1, 0, 2))
print_error_and_exit( oerr_cda() );
/* datatype = 24 is LONG RAW, = 8 is LONG */ if (odefin(&cda, 1, ucp, UCP_SIZE, 24, -1, /* if (odefin(&cda, 1, ucp, UCP_SIZE, 8, -1, */
&indp, (text *) 0, 0, -1, &retl, &rcode))
print_error_and_exit( oerr_cda() );
if (odefin(&cda, 2, file_name, sizeof(file_name), STRING_TYPE, -1,
&indp2, (text *) 0, 0, -1, &retl2, &rcode2))
print_error_and_exit( oerr_cda() );
for( rc = oexfet(&cda, (ub4) 1, 0, 0); !rc; rc = ofetch(&cda) ) {
output = fopen( file_name, "wb" );
if ( output == NULL )
print_error_and_exit( "fopen failed" );
for( fetched = 0, ret_len = 1; ret_len; fetched += ret_len )
{
if (oflng(&cda, 1, ucp, UCP_SIZE,
24, &ret_len, fetched ))
/* 8, &ret_len, fetched )) */
print_error_and_exit( oerr_cda() );
if ( ret_len )
{
if ( fwrite( ucp, 1, ret_len, output ) != ret_len )
print_error_and_exit( "fwrite failed" );
}
}
fclose( output );
printf( "Wrote File %s with %d bytes\n", file_name, fetched );
}
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |