Release 2 (8.1.6)
Part Number A76940-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Large Objects (LOBs) Release 2 (8.1.6) Part Number A76940-01 |
|
External LOBs (BFILEs), 23 of 41
|
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to read data from a BFILE.
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 specify an input amount of 4 Gb -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 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 is 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)
|
Note:
The most efficient way to read large amounts of LOB data is to use |
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 following examples read a photograph into PHOTO from a BFILE 'PHOTO_DIR'.
Examples are provided in these six programmatic environments:
/* Note that the example procedure readBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE readBFILE_proc IS Lob_loc BFILE := BFILENAME('PHOTO_DIR', 'Jefferson_photo'); Amount INTEGER := 32767; Position INTEGER := 1; Buffer RAW(32767); BEGIN /* Select the LOB: */ SELECT Photo INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* Open the BFILE: */ DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); /* Read data: */ DBMS_LOB.READ(Lob_loc, Amount, Position, Buffer); /* Close the BFILE: */ DBMS_LOB.CLOSE(Lob_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *dfnhp; text *selstmt = (text *) "SELECT Photo FROM Multimedia_tab WHERE Clip_ID=3"; /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } #define MAXBUFLEN 32767 void BfileRead(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *bfile_loc; ub1 bufp[MAXBUFLEN]; ub4 buflen, amt, offset; ub4 retval; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0); /* Select the bfile */ selectLob(bfile_loc, errhp, svchp, stmthp); checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, OCI_FILE_READONLY)); /* This example will READ the entire contents of a BFILE piecewise into a buffer using a standard polling method, processing each buffer piece after every READ operation until the entire BFILE has been read. */ /* Setting amt = 0 will read till the end of LOB*/ amt = 0; buflen = sizeof(bufp); /* Process the data in pieces */ offset = 1; memset(bufp, '\0', MAXBUFLEN); retval = OCILobRead(svchp, errhp, bfile_loc, &amt, offset, (dvoid *) bufp, buflen, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); /* Closing the BFILE is mandatory if you have opened it */ checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
IDENTIFICATION DIVISION. PROGRAM-ID. READ-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BFILE1 SQL-BFILE. 01 BUFFER2 PIC X(5) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL VAR BUFFER2 IS LONG RAW(5) END-EXEC. PROCEDURE DIVISION. READ-BFILE. * Allocate and initialize the CLOB locator EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT MUSIC INTO :BFILE1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 3 END-EXEC. * Open the BFILE EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Initiate polling read MOVE 0 TO AMT. EXEC SQL LOB READ :AMT FROM :BFILE1 INTO :BUFFER2 END-EXEC. * * Display the data here. * * Close and free the locator EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL LOB CLOSE :BFILE1 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC.
#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 4096 void readBFILE_proc() { OCIBFileLocator *Lob_loc; /* Amount and BufferLength are equal so only one READ is necessary: */ int Amount = BufferLength; 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 Photo INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; EXEC SQL WHENEVER NOT FOUND CONTINUE; /* Read data: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Read %d bytes\n", Amount); /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; readBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk As Variant Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) chunksize = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value OraMusic.offset = 1 OraMusic.PollingAmount = OraMusic.Size 'Read entire BFILE contents 'Open the Bfile for reading: OraMusic.Open amount_read = OraMusic.Read(chunk, chunksize) While OraMusic.Status = ORALOB_NEED_DATA amount_read = OraMusic.Read(chunk, chunksize) Wend OraMusic.Close
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 Ex4_53 { 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"); conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); try { BFILE src_lob = null; ResultSet rset = null; Boolean result = null; InputStream in = null; byte buf[] = new byte[1000]; int length = 0; boolean alreadyDisplayed = false; rset = stmt.executeQuery ( "SELECT music FROM multimedia_tab WHERE clip_id = 2"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Open the BFILE: src_lob.openFile(); // Get a handle to stream the data from the BFILE: in = src_lob.getBinaryStream(); // This loop fills the buf iteratively, retrieving data // from the InputStream: while ((in != null) && ((length = in.read(buf)) != -1)) { // the data has already been read into buf // We will only display the first CHUNK in this example: if (! alreadyDisplayed) { System.out.println("Bytes read in: " + Integer.toString(length)); System.out.println(new String(buf)); alreadyDisplayed = true; } } // Close the stream, BFILE, statement and connection: in.close(); src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|