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 |
|
External LOBs (BFILEs), 22 of 41
|
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to display BFILE data.
Not applicable.
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:
These examples open and display BFILE data.
Examples are provided in six programmatic environments:
/* Note that the example procedure displayBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE displayBFILE_proc IS File_loc BFILE; Buffer RAW(1024); Amount BINARY_INTEGER := 1024; Position INTEGER := 1; BEGIN /* Select the LOB: */ SELECT Music INTO File_loc FROM Multimedia_tab WHERE Clip_ID = 1; /* Opening the BFILE: */ DBMS_LOB.OPEN (File_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (File_loc, Amount, Position, Buffer); /* Display the buffer contents: */ DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); Position := Position + Amount; END LOOP; /* Closing the BFILE: */ DBMS_LOB.CLOSE (File_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); 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 Music 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 BfileDisplay(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { /* Assume all handles passed as input to this routine have been allocated and initialized */ OCILobLocator *bfile_loc; ub1 bufp[MAXBUFLEN]; ub4 buflen, amt, offset; boolean done; 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); done = FALSE; while (!done) { retval = OCILobRead(svchp, errhp, bfile_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 or last piece*/ /* 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*/ done = TRUE; break; case OCI_ERROR: /* report_error(); this function is not shown here */ done = TRUE; break; case OCI_NEED_DATA: /* There are 2 or more pieces */ /* process the data in bufp. amt will give the amount of data just read in bufp. This is in bytes for BFILEs and i characters for fixed width CLOBS and in bytes for variable width CLOBs */ break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); done = TRUE; break; } /* switch */ } /* while */ /* 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); }
This script is also provided in:
$ORACLE_HOME/rdbms/demo/lobs/cobol/fdisplay
IDENTIFICATION DIVISION. PROGRAM-ID. DISPLAY-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(9) VALUES "SAMP/SAMP". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 DEST-BLOB SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 01 BUFFER PIC X(5) VARYING. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 AMT PIC S9(9) COMP. 01 ORASLNRD PIC 9(4). EXEC SQL END DECLARE SECTION END-EXEC. 01 D-AMT PIC 99,999,99. EXEC SQL VAR BUFFER IS LONG RAW (100) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. DISPLAY-BFILE-DATA. * Connect to ORACLE EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE locator EXEC SQL ALLOCATE :SRC-BFILE END-EXEC. * Select the BFILE EXEC SQL SELECT PHOTO INTO :SRC-BFILE FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. * Open the BFILE EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. * Set the amount = 0 will initiate the polling method MOVE 0 TO AMT; EXEC SQL LOB READ :AMT FROM :SRC-BFILE INTO :BUFFER END-EXEC. * DISPLAY "BFILE DATA". * MOVE AMT TO D-AMT. * DISPLAY "First READ (", D-AMT, "): " BUFFER. * Do READ-LOOP until the whole BFILE is read. EXEC SQL WHENEVER NOT FOUND GO TO END-LOOP END-EXEC. READ-LOOP. EXEC SQL LOB READ :AMT FROM :SRC-BFILE INTO :BUFFER END-EXEC. * MOVE AMT TO D-AMT. * DISPLAY "Next READ (", D-AMT, "): " BUFFER. GO TO READ-LOOP. END-LOOP. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. * Close the LOB EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. * And free the LOB locator EXEC SQL FREE :SRC-BFILE END-EXEC. EXEC SQL ROLLBACK RELEASE 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 provided in:
$ORACLE_HOME/rdbms/demo/lobs/proc/fdisplay
/* This example will READ the entire contents of a BFILE piecewise into a buffer using a streaming mechanism via standard polling, displaying each buffer piece after every READ operation until the entire BFILE has been read: */ #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 displayBFILE_proc() { OCIBFileLocator *Lob_loc; int Amount; struct { 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(); EXEC SQL ALLOCATE :Lob_loc; /* Select the BFILE: */ EXEC SQL SELECT Music INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Setting Amount = 0 will initiate the polling method: */ Amount = 0; /* Set the maximum size of the Buffer: */ Buffer.Length = BufferLength; EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { /* Read a piece of the BFILE into the Buffer: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; printf("Display %d bytes\n", Buffer.Length); } printf("Display %d bytes\n", Amount); EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; displayBFILE_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.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-2001, Oracle Corporation. All Rights Reserved. |
|