Oracle8i Application Developer's Guide - Large Objects (LOBs) Release 2 (8.1.6) Part Number A76940-01 |
|
Internal Persistent LOBs, 19 of 42
See:
"Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs. |
This procedure describes how to read data from LOBs.
The most efficient way to read large amounts of LOB data is to use OCILobRead
() with the streaming mechanism enabled via polling or 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 - 1 regardless of the starting offset and the amount of data in the LOB. Hence, you do not need to incur a round-trip to the server to call OCILobGetLength
() to find out the length of the LOB value to determine the amount to read.
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 all 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)
amount
' parameter after each OCILobRead
() call to see how many bytes were read into the buffer since the buffer may not be entirely full.
len
' parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the 'len
' parameter during your callback processing since the entire buffer may not be filled with data (see Oracle Call Interface Programmer's Guide.).
A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB
when creating the table that contains the LOB
. This corresponds to the chunk size used by Oracle when accessing or modifying the LOB
value. Part of the chunk is used to store system-related information and the rest stores the LOB
value. The getchunksize
function returns the amount of space used in the LOB
chunk to store the LOB
value.
You will improve performance if you execute read
requests using a multiple of this chunk size. The reason for this is that you are using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, you should batch reads until you have enough for an entire chunk instead of issuing several LOB
read calls that operate on the same LOB
chunk.
See Chapter 3, "LOB Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:
The examples read data from a single video frame.
Examples are provided in the following programmatic environments:
/* Note that the example procedure readLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE readLOB_proc IS Lob_loc BLOB; Buffer RAW(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 1000; Chunksize INTEGER; BEGIN /* Select the LOB: */ SELECT Frame INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Find out the chunksize for this LOB column: */ Chunksize := DBMS_LOB.GETCHUNKSIZE(Lob_loc); IF (Chunksize < 32767) THEN Amount := (32767 / Chunksize) * Chunksize; END IF; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); /* Read data from the LOB: */ DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); END;
/* This example will READ the entire contents of a BLOB piecewise into a buffer using a standard polling method, processing each buffer piece after every READ operation until the entire BLOB has been read. */ #define MAXBUFLEN 1000 /* Select the locator into a locator variable */ sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *) "SELECT Frame \ FROM Multimedia_tab m WHERE m.Clip_ID = 3"; printf(" prepare statement in select_frame_locator\n"); checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); printf(" OCIDefineByPos in select_frame_locator\n"); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ printf(" OCIStmtExecute in select_frame_locator\n"); checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void readLOB_proc(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { ub4 amt; ub4 offset; sword retval; ub1 bufp[MAXBUFLEN]; ub4 buflen; boolean done; OCILobLocator *Lob_loc; OCILobLocator *blob; /* Allocate the Source (bfile) & destination (blob) locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the BLOB */ printf(" call select_frame4read_locator\n"); select_frame_locator(Lob_loc, errhp, svchp, stmthp); /* Open the BLOB */ printf(" call OCILobOpen\n"); checkerr (errhp, OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY)); /* Setting the amt to the buffer length. Note here that amt is in bytes since we are using a BLOB */ amt = 0; buflen = sizeof(bufp); /* Process the data in pieces */ printf(" process the data in piece\n"); offset = 1; memset(bufp, '\0', MAXBUFLEN); done = FALSE; while (!done) { retval = OCILobRead(svchp, errhp, Lob_loc, &amt, offset, (dvoid *) bufp, buflen, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); switch (retval) { case OCI_SUCCESS: /* Only one piece since amtp == bufp */ /* Process the data in bufp. amt will give the amount of data just read in bufp. This is in bytes for BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs */ printf("[%.*s]\n", buflen, bufp); done = TRUE; break; case OCI_ERROR: /* report_error(); this function is not shown here */ done = TRUE; break; case OCI_NEED_DATA: printf("[%.*s]\n", buflen, bufp); break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); done = TRUE; break; } } /* Closing the BLOB is mandatory if you have opened it */ checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
IDENTIFICATION DIVISION. PROGRAM-ID. ONE-READ-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BLOB1 SQL-BLOB. 01 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA 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 CLOB locator: EXEC SQL ALLOCATE :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT FRAME INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC. EXEC SQL LOB OPEN :BLOB1 END-EXEC. * Perform a single read: MOVE 32767 TO AMT. EXEC SQL LOB READ :AMT FROM :BLOB1 INTO :BUFFER2 END-EXEC. EXEC SQL LOB CLOSE :BLOB1 END-EXEC. END-OF-BLOB. DISPLAY "BUFFER2: ", BUFFER2(1:AMT). EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
#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 32767 void readLOB_proc() { OCIBlobLocator *Lob_loc; int Amount = BufferLength; /* Here (Amount == BufferLength) so only one READ is needed: */ char Buffer[BufferLength]; /* Datatype equivalencing is mandatory for this datatype: */ EXEC SQL VAR Buffer IS RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Frame INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Open the BLOB: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; EXEC SQL WHENEVER NOT FOUND CONTINUE; /* Read the BLOB data into the Buffer: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Read %d bytes\n", Amount); /* Close the BLOB: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; readLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
'Using OraClob.Read mechanism Dim MySession As OraSession Dim OraDb As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) Dim OraDyn as OraDynaset, OraStory as OraClob, amount_read%, chunksize%, chunk chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab", ORADYN_DEFAULT) Set OraStory = OraDyn.Fields("Story").Value OraStory.pollingAmount = OraStory.Size 'Read entire CLOB contents Do amount_read = OraStory.Read(chunk,chunksize) 'chunk returned is a variant of type byte array Loop Until OraStory.Status <> ORALOB_NEED_DATA
// Java IO classes: import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_79 { static final int MAXBUFSIZE = 32767; public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BLOB lob_loc = null; byte buf[] = new byte[MAXBUFSIZE]; ResultSet rset = stmt.executeQuery ( "SELECT frame FROM multimedia_tab WHERE clip_id = 1"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBLOB (1); } // MAXBUFSIZE is the number of bytes to read and 1000 is the offset from // which to start reading buf = lob_loc.getBytes(1000, MAXBUFSIZE); // Display the contents of the buffer here: System.out.println(new String(buf)); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|