Release 1 (9.0.1)
Part Number A88879-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Temporary LOBs, 11 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to read data from a temporary LOB.
The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled via polling or a callback.
When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes regardless of the starting offset and the amount of data in the LOB. You do not need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.
For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here's the OCI read call, excluding the initialization of the parameters:
#define MAX_LOB_SIZE 4294967295 ub4 amount = MAX_LOB_SIZE; ub4 offset = 1000; OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0)
When using polling mode, be sure to look at the value of the 'amount' parameter after each OCILobRead() call to see how many bytes were read into the buffer since the buffer may not be entirely full.
When using callbacks, the 'len' parameter, input to the callback, indicates how many bytes are filled in the buffer. Check the 'len' parameter during your callback processing since the entire buffer may not be filled with data (see theOracle Call Interface Programmer's Guide.).
See Chapter 3, "LOB Support in Different Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:
Our examples read the data from a single video Frame.
Examples are provided in the following programmatic environments:
/* Note that PL/SQL does not support streaming reads. The OCI example will
illustrate streaming reads: */ DECLARE Dest_loc BLOB; Src_loc BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio'); Amount INTEGER := 4000; Bbuf RAW(32767); Position INTEGER :=1; BEGIN DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE); /* Opening the FILE is mandatory: */ DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); /* Opening the LOB is optional: */ DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount); DBMS_LOB.READ (Dest_loc, Amount, Position, Bbuf); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE(Src_loc);
/* This is the same example as was shown for reading and displaying data from a temporary LOB. This function takes the Washinton_audio file, opens that file as a BFILE as input, loads that file data into a temporary LOB and then reads the data from the temporary LOB 5000 or less bytes at a time. 5000 bytes was an arbitrary maximum buffer length chosen for this example. The function returns 0 if it completes successfully, and -1 if it fails. */ #define MAXBUFLEN 32767 sb4 test_file_to_lob (OCILobLocator *lob_loc, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { int rowind; OCILobLocator *bfile; ub4 amount = 14000; ub4 offset =0; ub4 loblen = 0; ub4 amtp = 0; sword retval; ub4 piece = 1; ub4 remainder=0; ub1 bufp[MAXBUFLEN]; (void) printf( "\n===> Testing loading files into lobs and displaying them\n\n"); if (OCIDescriptorAlloc((dvoid **)&bfile, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0)) /* Create a temporary LOB: */ if(OCILobCreateTemporary(svchp, errhp, lob_loc, (ub2)0, SQLCS_IMPLICIT, OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)) { (void) printf("FAILED: CreateTemporary() \n"); return -1; } if(OCILobFileSetName(envhp, errhp, &bfile,(text*)"AUDIO_DIR", (ub2)strlen("AUDIO_DIR"), (text*)"Washington_audio", (ub2)strlen("Washington_audio"))) { printf("OCILobFileSetName FAILED\n"); return -1; } if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY)) { printf( "OCILobFileOpen FAILED \n"); return -1; } if(OCILobLoadFromFile(svchp,errhp,lob_loc,(OCILobLocator*)bfile,(ub4)amount, (ub4)1,(ub4)1)) { printf( "OCILobLoadFromFile FAILED\n"); return -1; } offset = 1; memset(bufp, '\0', MAXBUFLEN); retval = OCILobRead(svchp, errhp, lob_loc, &amtp, offset, (dvoid *) bufp, (amount < MAXBUFLEN ? amount : MAXBUFLEN), (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); fprintf(stderr,"1st piece read from file is %s\n",bufp); switch (retval) { case OCI_SUCCESS: /* Only one piece */ (void) printf("stream read piece # %d \n", ++piece); (void)printf("piece read was %s\n",bufp); break; case OCI_ERROR: /* report_error(); function not shown here */ break; case OCI_NEED_DATA: /* There are 2 or more pieces */ remainder = amount; fprintf(stderr,"remainder is %d \n",remainder); do { memset(bufp, '\0', MAXBUFLEN); amtp = 0; remainder -= MAXBUFLEN; fprintf(stderr,"remainder is %d \n",remainder); retval = OCILobRead(svchp, errhp, lob_loc, &amtp, offset, (dvoid *) bufp,(ub4) MAXBUFLEN, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); /* The amount read returned is undefined for FIRST, NEXT pieces: */ (void)fprintf(stderr,"stream read %d th piece, amtp = %d\n", ++piece, amtp); (void)fprintf(stderr, "piece of length read was %d\n",strlen((const char *)bufp)); (void)fprintf(stderr,"piece read was %s\n",bufp); } while (retval == OCI_NEED_DATA); break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); break; } /* Close the audio file: */ if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile)) { printf( "OCILobFileClose FAILED\n"); return -1; } if (OCIDescriptorFree ((dvoid*) lob_loc, (ub4) OCI_DTYPE_LOB)) { printf ("failed in OCIDescriptor Free\n"); return -1; } /* Clean up the temp LOB now that we are done with it: */ if(check_and_free_temp(lob_loc, errhp, svchp,stmthp, envhp)) { printf("check and free failed in load test\n"); return -1; } return 0; } sb4 check_and_free_temp(OCILobLocator *tblob, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stnthp, OCIEnv *envhp) { boolean is_temp; is_temp = FALSE; if (OCILobIsTemporary (envhp,errhp, tblob, &is_temp)) { printf ("FAILED: OciLobIsTemporary call \n"); } if(is_temp) { if (OCILobFreeTemporary (svchp, errhp,tblob)) { printf ("FAILED: OCILobFreeTemporary call \n"); return -1; } else { printf ("Temporary LOB freed\n"); } }else { printf ("locator is not a temporary LOB locator\n"); } return 0; }
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/tread
IDENTIFICATION DIVISION. PROGRAM-ID. ONE-READ-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(9) VALUES "SAMP/SAMP". 01 TEMP-BLOB SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. 01 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 ORASLNRD PIC 9(4). 01 ISTEMP PIC S9(9) COMP. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC. PROCEDURE DIVISION. ONE-READ-BLOB. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BLOB locator: EXEC SQL ALLOCATE :SRC-BFILE END-EXEC. EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. * Set up the directory and file information: MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "Washington_audio" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. EXEC SQL LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. EXEC SQL LOB DESCRIBE :SRC-BFILE GET LENGTH INTO :AMT END-EXEC. * Open source BFILE and destination temporary BLOB: EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC. EXEC SQL LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB END-EXEC. * Perform a single read: EXEC SQL LOB READ :AMT FROM :TEMP-BLOB INTO :BUFFER2 END-EXEC. DISPLAY "Read ", BUFFER2, " from TEMP-BLOB". END-OF-BLOB. EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC. EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC. EXEC SQL FREE :TEMP-BLOB END-EXEC. EXEC SQL FREE :SRC-BFILE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. MOVE ORASLNR TO ORASLNRD. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/proc/tread
/* Read Data from a Temporary LOB */ #include <oci.h> #include <stdio.h> #include <sqlca.h> void Sample_Error() { EXEC SQL WHENEVER SQLERROR CONTINUE; printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } #define BufferLength 1024 void readTempLOB_proc() { OCIBlobLocator *Temp_loc; OCIBFileLocator *Lob_loc; char *Dir = "AUDIO_DIR", *Name = "Washington_audio"; int Length, Amount; struct { unsigned short Length; char Data[BufferLength]; } Buffer; /* Datatype Equivalencing is Mandatory for this Datatype */ EXEC SQL VAR Buffer IS VARRAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the BFILE Locator */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; /* Determine the Length of the BFILE */ EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length; /* Allocate and Create the Temporary LOB */ EXEC SQL ALLOCATE :Temp_loc; EXEC SQL LOB CREATE TEMPORARY :Temp_loc; /* Open the BFILE for Reading */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Load the BFILE into the Temporary LOB */ Amount = Length; EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc; /* Close the BFILE */ EXEC SQL LOB CLOSE :Lob_loc; Buffer.Length = BufferLength; EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { /* Read a piece of the Temporary LOB into the Buffer */ EXEC SQL LOB READ :Amount FROM :Temp_loc INTO :Buffer; printf("Read %d bytes\n", Buffer.Length); } printf("Read %d bytes\n", Amount); /* Free the Temporary LOB */ EXEC SQL LOB FREE TEMPORARY :Temp_loc; /* Release resources held by the Locators */ EXEC SQL FREE :Temp_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; readTempLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|