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 |
|
Internal Persistent LOBs, 32 of 42
|
See:
"Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs. |
This procedure describes how to write data to 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 a callback. If you know how much data will be written to the LOB, specify that amount when calling OCILobWrite(). This will allow for the contiguity of the LOB data on disk. Apart from being spatially efficient, the contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.
A chunk is one or more Oracle blocks. As noted previously, 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/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 write requests using a multiple of this chunk size. The reason for this is that the LOB chunk is versioned for every write operation. If all writes are done on a chunk basis, no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, you should batch writes until you have enough for an entire chunk instead of issuing several LOB write calls that operate on the same LOB chunk.
Prior to updating a LOB value via the PL/SQL DBMS_LOB package or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.
For more details on the state of the locator after an update, refer to "Updated LObs Via Updated Locators" in Chapter 5, "Advanced Topics".
When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:
amount parameter should be <= the buffer length parameter
length of the buffer should be ((amount*2) - 1). This guideline exists because the two characters of the string are seen as one hexadecimal character (and an implicit hexadecimal-to-raw conversion takes place), i.e., every two bytes of the string are converted to one raw byte.
The following example is correct:
declare blob_loc BLOB; rawbuf RAW(10); an_offset INTEGER := 1; an_amount BINARY_INTEGER := 10; begin select blob_col into blob_loc from a_table where id = 1; rawbuf := '1234567890123456789'; dbms_lob.write(blob_loc, an_amount, an_offset, rawbuf); commit; end;
Replacing the value for 'an_amount' in the previous example with the following values, yields error message, ora_21560:
an_amount BINARY_INTEGER := 11;
or
an_amount BINARY_INTEGER := 19;
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 allow the STORY data (the storyboard for the clip) to be updated by writing data to the LOB.
Examples are provided in the following programmatic environments:
/* Note that the example procedure writeDataToLOB_proc is not part of the DBMS_LOB package. */ CREATE or REPLACE PROCEDURE writeDataToLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 1; i INTEGER; BEGIN /* Select a LOB: */ SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Fill the buffer with data to write to the LOB: */ FOR i IN 1..3 LOOP DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer); /* Fill the buffer with more data to write to the LOB: */ Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); END; /* We add a second example to show a case in which the buffer size and amount differs from the first example: */ CREATE or REPLACE PROCEDURE writeDataToLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER; i INTEGER; Chunk_size INTEGER; BEGIN SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE; /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); Chunk_size := DBMS_LOB.GETCHUNKSIZE(Lob_loc); /* Fill the buffer with 'Chunk_size' worth of data to write to the LOB. Use the chunk size (or a multiple of chunk size) when writing data to the LOB. Make sure that you write within a chunk boundary and don't overlap different chunks within a single call to DBMS_LOB.WRITE. */ Amount := Chunk_size; /* Write data starting at the beginning of the second chunk: */ Position := Chunk_size + 1; FOR i IN 1..3 LOOP DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer); /* Fill the buffer with more data (of size Chunk_size) to write to the LOB: */ Position := Position + Amount; END LOOP; /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE (Lob_loc); END;
/* This example illustrates OCI's ability to write arbitrary amounts of data to an Internal LOB in either a single piece or in multiple pieces using streaming with standard polling. A dynamically allocated Buffer is used to hold the data being written to the LOB. */ /* Select the locator into a locator variable */ sb4 select_lock_story_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *) "SELECT Story FROM Multimedia_tab m \ WHERE m.Clip_ID = 1 FOR UPDATE"; OCIDefine *defnp1; 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 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); } #define MAXBUFLEN 32767 void writeDataToLob(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIClobLocator *Lob_loc; ub4 Total = 2.5*MAXBUFLEN; /* <total amount of data to write to the CLOB in bytes> */ unsigned int amt; unsigned int offset; unsigned int remainder, nbytes; boolean last; ub1 bufp[MAXBUFLEN]; sb4 err; /* Allocate the locators desriptors*/ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); /* Select the CLOB */ printf (" select a story Lob\n"); select_lock_story_locator(Lob_loc, errhp, svchp, stmthp); /* Open the CLOB */ checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE))); 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 */ amt = 0; offset = 1; printf(" write the Lob data in pieces\n"); if (0 == remainder) { amt = nbytes; /* Here, (Total <= MAXBUFLEN ) so we can write in one piece */ checkerr (errhp, OCILobWrite (svchp, errhp, Lob_loc, &amt, 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, &amt, 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, &amt, 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, &amt, 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 LOB 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); return; }
IDENTIFICATION DIVISION. PROGRAM-ID. WRITE-CLOB. ENVIRONMENT DIVISION. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT INFILE ASSIGN TO "datfile.dat" ORGANIZATION IS SEQUENTIAL. DATA DIVISION. FILE SECTION. FD INFILE RECORD CONTAINS 5 CHARACTERS. 01 INREC PIC X(5). WORKING-STORAGE SECTION. 01 CLOB1 SQL-CLOB. 01 BUFFER PIC X(5) VARYING. 01 AMT PIC S9(9) COMP VALUES 321. 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. 01 USERID PIC X(11) VALUES "SAMP/SAMP". EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. WRITE-CLOB. EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Open the input file: OPEN INPUT INFILE. * 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. * 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 5 TO BUFFER-LEN. IF (END-OF-FILE = "Y") EXEC SQL LOB WRITE ONE :AMT FROM :BUFFER INTO :CLOB1 AT :OFFSET END-EXEC ELSE DISPLAY "LOB WRITE FIRST: ", BUFFER-ARR EXEC SQL LOB WRITE FIRST :AMT FROM :BUFFER INTO :CLOB1 AT :OFFSET END-EXEC. * Continue reading from the input data file * and writing to the CLOB: PERFORM READ-NEXT-RECORD. PERFORM WRITE-TO-CLOB UNTIL END-OF-FILE = "Y". MOVE INREC TO BUFFER-ARR. MOVE 1 TO BUFFER-LEN. DISPLAY "LOB WRITE LAST: ", BUFFER-ARR(1:BUFFER-LEN). EXEC SQL LOB WRITE LAST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN. WRITE-TO-CLOB. IF ( END-OF-FILE = "N") MOVE INREC TO BUFFER-ARR. MOVE 5 TO BUFFER-LEN. DISPLAY "LOB WRITE NEXT: ", BUFFER-ARR(1:BUFFER-LEN). EXEC SQL LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1 END-EXEC. PERFORM READ-NEXT-RECORD. READ-NEXT-RECORD. MOVE SPACES TO INREC. READ INFILE NEXT RECORD AT END MOVE "Y" TO END-OF-FILE. DISPLAY "END-OF-FILE IS " END-OF-FILE. 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.
/* 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 of in multiple pieces using a Streaming Mechanism that utilizes standard polling. A dynamically allocated Buffer is used to hold the data being written to the LOB: */ #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 1024 void writeDataToLOB_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 CLOB: */ 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 we 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; /* Here, (remainder <= BufferLength) */ 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 CLOB: */ EXEC SQL LOB CLOSE :Lob_loc; /* Free resources held by the Locator: */ EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; writeDataToLOB_proc(1); EXEC SQL ROLLBACK WORK; writeDataToLOB_proc(4); EXEC SQL ROLLBACK WORK RELEASE; }
'There are two ways of writing a lob using orablob.write or orablob.copyfromfile 'Using OraBlob.Write mechanism Dim OraDyn As OraDynaset, OraSound As OraBlob, amount_written%, chunksize%, curchunk() As Byte chunksize = 32767 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", ORADYN_DEFAULT) Set OraSound = OraDyn.Fields("Sound").Value Oradyn.Edit OraSound.CopyFromFile "c:\mysound.aud" Oradyn.Update
import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; 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_126 { 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 dest_loc = null; byte[] buf = new byte[MAXBUFSIZE]; long pos = 0; ResultSet rset = stmt.executeQuery ( "SELECT frame FROM multimedia_tab WHERE clip_id = 1 FOR UPDATE"); if (rset.next()) { dest_loc = ((OracleResultSet)rset).getBLOB (1); } // Start writing at the end of the LOB. ie. append: pos = dest_loc.length(); // fill buf with contents to be written: buf = (new String("Hello World")).getBytes(); // Write the contents of the buffer into position pos of the output LOB: dest_loc.putBytes(pos, buf); // Close all streams and handles: stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|