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 |
|
Internal Persistent LOBs, 19 of 43
|
See:
"Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations. |
This procedure describes how to display LOB data.
The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled.
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:
As an example of displaying a LOB, our scenario stream-reads the image Drawing from the column object Map_obj onto the client-side in order to view the data.
Examples are provided in the following programmatic environments:
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/pls/idisplay.
/* Note that the example procedure displayLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE displayLOB_proc IS Lob_loc BLOB; Buffer RAW(1024); Amount BINARY_INTEGER := 1024; Position INTEGER := 1; BEGIN /* Select the LOB: */ SELECT m.Map_obj.Drawing INTO Lob_loc FROM Multimedia_tab m WHERE m.Clip_ID = 1; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); LOOP DBMS_LOB.READ (Lob_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 LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data'); END;
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/oci/idisplay.
/* 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 32767 /* Select the locator into a locator variable */ sb4 select_mapobjectdrawing_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *) "SELECT m.Map_obj.Drawing \ FROM Multimedia_tab m WHERE m.Clip_ID = 1"; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); 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 */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void displayLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIBlobLocator *Lob_loc; ub4 amt; ub4 offset; sword retval; boolean done; ub1 bufp[MAXBUFLEN]; ub4 buflen; OCILobLocator *Lob_Loc; /* 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(" select the mapobjectdrawing locator...\n"); select_mapobjectdrawing_locator(Lob_loc, errhp, svchp, stmthp); /* Open the BLOB */ printf(" open the lob\n"); checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READONLY))); /* Setting amt = 0 will read till the end of LOB*/ amt = 0; buflen = sizeof(bufp); /* Process the data in pieces */ printf(" Process the data in pieces\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 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: checkerr (errhp, retval); 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 BLOBs and in characters for fixed width CLOBS and in bytes for variable width CLOBs */ break; default: checkerr (errhp, retval); done = TRUE; break; } } /* while */ /* Closing the BLOB is mandatory if you have opened it */ printf(" close the lob \n"); checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); }
This script is provided at $ORACLE_HOME/rdbms/demo/lobs/cobol/idisplay
IDENTIFICATION DIVISION. PROGRAM-ID. DISPLAY-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BLOB1 SQL-BLOB. 01 BUFFER2 PIC X(5) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 D-AMT PIC 9. EXEC SQL VAR BUFFER2 IS RAW(5) END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. DISPLAY-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 :BLOB1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC. EXEC SQL SELECT M.SOUND INTO :BLOB1 FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 1 END-EXEC. DISPLAY "Found column SOUND". * Initiate polling read: MOVE 0 TO AMT. EXEC SQL LOB READ :AMT FROM :BLOB1 AT :OFFSET INTO :BUFFER2 END-EXEC. DISPLAY " ". MOVE AMT TO D-AMT. DISPLAY "first read (", D-AMT, "): " BUFFER2. READ-BLOB-LOOP. MOVE " " TO BUFFER2. EXEC SQL LOB READ :AMT FROM :BLOB1 INTO :BUFFER2 END-EXEC. MOVE AMT TO D-AMT. DISPLAY "next read (", D-AMT, "): " BUFFER2. GO TO READ-BLOB-LOOP. END-OF-BLOB. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BLOB1 END-EXEC. MOVE AMT TO D-AMT. DISPLAY "last read (", D-AMT, "): " BUFFER2(1:AMT). 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.
You can find this script at $ORACLE_HOME/rdbms/demo/lobs/proc/ichecko
/* 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: */ #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 displayLOB_proc() { OCIBlobLocator *Lob_loc; int 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(); EXEC SQL ALLOCATE :Lob_loc; /* Select the BLOB: */ EXEC SQL SELECT m.Map_obj.Drawing INTO Lob_loc FROM Multimedia_tab m WHERE m.Clip_ID = 1; /* Open the BLOB: */ 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 BLOB into the Buffer: */ EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer; /* Process (Buffer.Length == BufferLength) amount of Buffer.Data */ } /* Process (Buffer.Length == Amount) amount of Buffer.Data */ /* Closing the BLOB is mandatory if you have opened it: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; displayLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
You can find this script at $ORACLE_HOME/rdbms/demo/lobs/vbasic/idisplay.
'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 contentsDo'chunk returned is a variant of type byte array:amount_read = OraStory.Read(chunk, chunksize) 'Msgbox chnunk Loop Until OraStory.Status <> ORALOB_NEED_DATA
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/java/idisplay.
// Core JDBC classes: import java.io.OutputStream; 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_72 { 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; InputStream in = null; byte buf[] = new byte[MAXBUFSIZE]; int pos = 0; int length = 0; ResultSet rset = stmt.executeQuery ( "SELECT m.map_obj.drawing FROM multimedia_tab m WHERE m.clip_id = 1"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBLOB (1); } // read this LOB through an InputStream: in = lob_loc.getBinaryStream(); while ((length = in.read(buf)) != -1) { pos += length; System.out.println(Integer.toString(pos)); // Process the contents of the buffer here. } in.close(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|