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, 18 of 43
|
See:
"Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations. |
This procedure describes how to check in a LOB.
The most efficient way to write large amounts of LOB data is to use OCILobWrite() with the streaming mechanism enabled via polling or callback
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:
The checkin operation demonstrated here follows from "Checking Out a LOB". In this case, the procedure writes the data back into the CLOB Transcript column within the nested table InSeg_ntab that contains interview segments. As noted above, you should the OCI or PRO*C interface with streaming for the underlying write operation; using DBMS_LOB.WRITE will result in non-optimal performance.
The following examples illustrate how to checkin a LOB using various programmatic environments:
Examples are provided in the following programmatic environments:
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/pls/ichkin.
/* Note that the example procedure checkInLOB_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE checkInLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 2147483647; i INTEGER; BEGIN /* Select the LOB: */ SELECT Intab.Transcript INTO Lob_loc FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab WHERE Clip_ID = 2) Intab WHERE Intab.Segment = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE) FOR i IN 1..3 LOOP /* Fill the Buffer with data to be written. */ /* Write data: */ DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer); Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); END;
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/oci/ichkin.
/* This example demonstrates how OCI provides for the ability to write arbitrary amounts of data to an Internal LOB in either a single piece of in multiple pieces using a streaming mechanism that utilizes standard polling. A statically allocated Buffer is used to hold the data being written to the LOB. */ #define MAXBUFLEN 32767 /* Select the locator into a locator variable */ sb4 select_lock_transcript_locator(Lob_loc, errhp, stmthp,svchp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *defnp1; text *sqlstmt = (text *) "SELECT Intab.Transcript \ FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab \ WHERE Mtab.Clip_ID = 2) Intab \ WHERE Intab.Segment = 1 FOR UPDATE"; 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_CLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return OCI_SUCCESS; } void checkinLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIClobLocator *Lob_loc; ub4 Total = 2.5*MAXBUFLEN; ub4 amtp; ub4 offset; ub4 remainder; ub4 nbytes; boolean last; ub1 bufp[MAXBUFLEN]; sb4 err; /* Allocate locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB,(size_t) 0,(dvoid **) 0); /* Select the CLOB */ printf(" select the transcript locator...\n"); select_lock_transcript_locator(Lob_loc, errhp, stmthp, svchp); /* Open the CLOB */ printf (" open the locator.\n"); checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); printf (" write the lob in pieces\n"); if (Total > MAXBUFLEN) nbytes = MAXBUFLEN; /* We will use streaming via standard polling */ else nbytes = Total; /* Only a single write is required */ /* Fill the buffer with nbytes worth of data */ remainder = Total - nbytes; /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */ amtp = 0; /* offset = <Starting position where to begin writing the data>; */ offset = 1; if (0 == remainder) { amtp = nbytes; /* Here, (Total <= MAXBUFLEN ) so we can write in one piece */ checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, amtp, offset, bufp, nbytes, OCI_ONE_PIECE, (dvoid *) 0, (sb4 (*)(dvoid *,dvoid *,ub4 *,ub1 *)) 0, 0, SQLCS_IMPLICIT)); } else { /* Here (Total > MAXBUFLEN ) so we use streaming via standard polling */ /* write the first piece. Specifying first initiates polling. */ err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes, OCI_FIRST_PIECE, (dvoid *) 0, (sb4 (*)(dvoid *,dvoid *,ub4 *,ub1 *)) 0, 0, SQLCS_IMPLICIT); if (err != OCI_NEED_DATA) checkerr (errhp, err); last = FALSE; /* write the next (interim) and last pieces */ do { if (remainder > MAXBUFLEN) nbytes = MAXBUFLEN; /* Still have more pieces to go */ else { nbytes = remainder; /* Here, (remainder <= MAXBUFLEN) */ last = TRUE; /* This is going to be the Final piece */ } /* Fill the buffer with nbytes worth of data */ if (last) { /* Specifying LAST terminates polling */ err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes, OCI_LAST_PIECE, (dvoid *) 0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_SUCCESS) checkerr(errhp, err); } else { err = OCILobWrite (svchp, errhp, Lob_loc, &amtp, offset, bufp, nbytes, OCI_NEXT_PIECE, (dvoid *) 0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != OCI_NEED_DATA) checkerr (errhp, err); } /* Determine how much is left to write */ remainder = remainder - nbytes; } while (!last); } /* At this point, (remainder == 0) */ /* 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); }
This script is provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/ichkin
IDENTIFICATION DIVISION. PROGRAM-ID. CHECKIN. ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT INFILE ASSIGN TO "datfile.dat" ORGANIZATION IS SEQUENTIAL. DATA DIVISION. FILE SECTION. FD INFILE RECORD CONTAINS 80 CHARACTERS. 01 INREC PIC X(80). WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 CLOB1 SQL-CLOB. 01 BUFFER PIC X(80) VARYING. 01 AMT PIC S9(9) COMP VALUE 0. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 END-OF-FILE PIC X(1) VALUES "N". 01 D-BUFFER-LEN PIC 9. 01 D-AMT PIC 9. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. WRITE-CLOB. 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 :CLOB1 END-EXEC. EXEC SQL SELECT STORY INTO :CLOB1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 FOR UPDATE END-EXEC. * Open the input file for reading: OPEN INPUT INFILE. * Either write entire record or write first piece. * Read a data file here and populate BUFFER-ARR and BUFFER-LEN. * END-OF-FILE will be set to "Y" when the entire file has been * read. PERFORM READ-NEXT-RECORD. MOVE INREC TO BUFFER-ARR. MOVE 80 TO BUFFER-LEN. IF (END-OF-FILE = "Y") MOVE 80 TO AMT EXEC SQL LOB WRITE ONE :AMT FROM :BUFFER INTO :CLOB1 AT :OFFSET END-EXEC ELSE DISPLAY "LOB WRITE FIRST" DISPLAY BUFFER-ARR MOVE 321 TO AMT EXEC SQL LOB WRITE FIRST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC END-IF. * Continue reading from the input data file * and writing to the CLOB: PERFORM READ-WRITE UNTIL END-OF-FILE = "Y". PERFORM SIGN-OFF. STOP RUN. READ-WRITE. PERFORM READ-NEXT-RECORD. MOVE INREC TO BUFFER-ARR. DISPLAY "READ-WRITE". DISPLAY INREC. MOVE 80 TO BUFFER-LEN. IF END-OF-FILE = "Y" DISPLAY "LOB WRITE LAST: ", BUFFER-ARR MOVE 1 TO BUFFER-LEN EXEC SQL LOB WRITE LAST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC ELSE DISPLAY "LOB WRITE NEXT: ", BUFFER-ARR MOVE 0 TO AMT EXEC SQL LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1 END-EXEC END-IF. READ-NEXT-RECORD. MOVE SPACES TO INREC. READ INFILE NEXT RECORD AT END MOVE "Y" TO END-OF-FILE. SIGN-OFF. CLOSE INFILE. EXEC SQL FREE :CLOB1 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.
You can find this script at $ORACLE_HOME/rdbms/demo/lobs/proc/ichecki
/* This example demonstrates how Pro*C/C++ provides for the ability to WRITE arbitrary amounts of data to an Internal LOB in either a single piece or in multiple pieces using a Streaming Mechanism that utilizes standard polling. A static Buffer is used to hold the data being written: */ #include <oci.h> #include <stdio.h> #include <string.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 512 void checkInLOB_proc(multiple) int multiple; { OCIClobLocator *Lob_loc; VARCHAR Buffer[BufferLength]; unsigned int Total; unsigned int Amount; unsigned int remainder, nbytes; boolean last; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the Locator: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Story INTO :Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Open the LOB: */ EXEC SQL LOB OPEN :Lob_loc READ WRITE; Total = Amount = (multiple * BufferLength); if (Total > BufferLength) nbytes = BufferLength; /* We will use streaming via standard polling */ else nbytes = Total; /* Only a single WRITE is required */ /* Fill the Buffer with nbytes worth of data: */ memset((void *)Buffer.arr, 32, nbytes); Buffer.len = nbytes; /* Set the Length */ remainder = Total - nbytes; if (0 == remainder) { /* Here, (Total <= BufferLength) so we can WRITE in ONE piece: */ EXEC SQL LOB WRITE ONE :Amount FROM :Buffer INTO :Lob_loc; printf("Write ONE Total of %d characters\n", Amount); } else { /* Here (Total > BufferLength) so use streaming via standard polling: WRITE the FIRST piece. Specifying FIRST initiates polling: */ EXEC SQL LOB WRITE FIRST :Amount FROM :Buffer INTO :Lob_loc; printf("Write FIRST %d characters\n", Buffer.len); last = FALSE; /* WRITE the NEXT (interim) and LAST pieces: */ do { if (remainder > BufferLength) nbytes = BufferLength; /* Still have more pieces to go */ else { nbytes = remainder; last = TRUE; /* This is going to be the Final piece */ } /* Fill the Buffer with nbytes worth of data: */ memset((void *)Buffer.arr, 32, nbytes); Buffer.len = nbytes; /* Set the Length */ if (last) { EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Specifying LAST terminates polling: */ EXEC SQL LOB WRITE LAST :Amount FROM :Buffer INTO :Lob_loc; printf("Write LAST Total of %d characters\n", Amount); } else { EXEC SQL WHENEVER SQLERROR DO break; EXEC SQL LOB WRITE NEXT :Amount FROM :Buffer INTO :Lob_loc; printf("Write NEXT %d characters\n", Buffer.len); } /* Determine how much is left to WRITE: */ remainder = remainder - nbytes; } while (!last); } EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* At this point, (Amount == Total), the total amount that was written */ /* Close the LOB: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; checkInLOB_proc(1); EXEC SQL ROLLBACK WORK; checkInLOB_proc(4); EXEC SQL ROLLBACK WORK RELEASE; }
You can find this script at $ORACLE_HOME/rdbms/demo/lobs/vbasic/ichkin
'There are two ways of writing a lob using orablob.write or orablob.copyfromfile 'Using OraBlob.Write mechanism Dim MySession As OraSession Dim OraDb As OraDatabase Dim fnum As Integer Dim OraDyn As OraDynaset, OraSound As OraBlob, amount_written%, chunksize%, curchunk() As Byte Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&) chunksize = 500 ReDim curchunk(chunksize) Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").Value fnum = FreeFile Open "c:\tmp\washington_audio" For Binary As #fnum OraSound.offset = 1 OraSound.pollingAmount = LOF(fnum) remainder = LOF(fnum) Dim piece As Byte Get #fnum, , curchunk OraDyn.Edit piece = ORALOB_FIRST_PIECE OraSound.Write curchunk, chunksize, ORALOB_FIRST_PIECE While OraSound.Status = ORALOB_NEED_DATA remainder = remainder - chunksize If remainder <= chunksize Then chunksize = remainder piece = ORALOB_LAST_PIECE Else piece = ORALOB_NEXT_PIECE End If Get #fnum, , curchunk OraSound.Write curchunk, chunksize, piece Wend OraDyn.Update 'Using OraBlob.CopyFromFile mechanism Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_ id", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").Value OraDyn.Edit OraSound.CopyFromFile "c:\tmp\washington_audio" OraDyn.Update
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/java/ichkin.
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_66 { 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 { CLOB lob_loc = null; String buf = new String ("Some Text To Write"); ResultSet rset = stmt.executeQuery ( "SELECT story FROM multimedia_tab WHERE clip_id = 2 FOR UPDATE"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getCLOB (1); } long pos = 0; // Offset within the CLOB where the data is to be written long length = 0; // This is the size of the buffer to be written // This loop writes the buffer three times consecutively: for (int i = 0; i < 3; i++) { pos = lob_loc.length(); // an alternative is: lob_loc.putString(pos, buf); lob_loc.putString(pos, buf); // Some debug information: System.out.println(" putString(" + Long.toString(pos) +" buf);"); } stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|