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 |
|
Temporary LOBs, 24 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to write data to a temporary 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, contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.
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 example procedures 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 writeDataToTempLOB_proc is not part of the DBMS_LOB package. */ CREATE or REPLACE PROCEDURE writeDataToTempLOB_proc IS Lob_loc CLOB; Buffer VARCHAR2(26); Amount BINARY_INTEGER := 26; Position INTEGER := 1; i INTEGER; BEGIN DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE); /* Opening the LOB is optional: */ DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE); /* Fill the buffer with data to write to the LOB: */ Buffer := 'abcdefghijklmnopqrstuvwxyz'; 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); DBMS_LOB.FREETEMPORARY(Lob_loc); END;
/* This example illustrates streaming writes with polling */ #define MAXBUFLEN 32767 sb4 write_temp_lobs (OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { OCIClobLocator *tclob; unsigned int Total = 40000; unsigned int amtp; unsigned int offset; unsigned int remainder, nbytes; boolean last; ub1 bufp[MAXBUFLEN]; sb4 err; /* Allocate the locators descriptors: */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tclob , (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0); if(OCILobCreateTemporary(svchp, errhp, tclob, (ub2)0, SQLCS_IMPLICIT, OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)) { (void) printf("FAILED: CreateTemporary() \n"); return -1; } /* Open the CLOB: */ checkerr (errhp, (OCILobOpen(svchp, errhp, tclob, 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: */ memset(bufp,'a',32767); remainder = Total - nbytes; amtp = 0; offset = 1; /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE: */ if (0 == remainder) { amtp = nbytes; /* Here, (Total <= MAXBUFLEN ) so we can WRITE in ONE piece: */ checkerr (errhp, OCILobWrite (svchp, errhp, tclob, &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, tclob, &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, tclob, &amtp, offset, bufp, nbytes, OCI_LAST_PIECE, (dvoid *)0, (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0, 0, SQLCS_IMPLICIT); if (err != 0) checkerr (errhp, err); } else { err = OCILobWrite (svchp, errhp, tclob, &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 LOB is mandatory if you have opened it: */ checkerr (errhp, OCILobClose(svchp, errhp, tclob)); /* Free the temporary LOB: */ checkerr(errhp,OCILobFreeTemporary(svchp,errhp,tclob)); /* Free resources held by the locators: */ (void) OCIDescriptorFree((dvoid *) tclob, (ub4) OCI_DTYPE_LOB); }
: Write Data to a Temporary LOB IDENTIFICATION DIVISION. PROGRAM-ID. WRITE-TEMP. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 TEMP-CLOB SQL-CLOB. 01 BUFFER PIC X(20) VARYING. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(20) VARYING. 01 DIR-IND PIC S9(4) COMP. 01 FNAME-IND PIC S9(4) COMP. 01 AMT PIC S9(9) COMP VALUE 10. 01 ORASLNRD PIC 9(4). EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. PROCEDURE DIVISION. WRITE-TEMP. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BFILE and BLOB locators: EXEC SQL ALLOCATE :TEMP-CLOB END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-CLOB END-EXEC. EXEC SQL LOB OPEN :TEMP-CLOB READ WRITE END-EXEC. MOVE "ABCDE12345ABCDE12345" TO BUFFER-ARR. MOVE 20 TO BUFFER-LEN. MOVE 20 TO AMT. * Append the data in BUFFER to TEMP-CLOB: EXEC SQL LOB WRITE :AMT FROM :BUFFER INTO :TEMP-CLOB END-EXEC. * Close the LOBs: EXEC SQL LOB CLOSE :TEMP-CLOB END-EXEC. * Free the temporary LOB: EXEC SQL LOB FREE TEMPORARY :TEMP-CLOB END-EXEC. * And free the LOB locators: EXEC SQL FREE :TEMP-CLOB 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.
#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 writeDataToTempLOB_proc(multiple) int multiple; { OCIClobLocator *Temp_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 Temporary LOB: */ EXEC SQL ALLOCATE :Temp_loc; EXEC SQL LOB CREATE TEMPORARY :Temp_loc; /* Open the Temporary LOB: */ EXEC SQL LOB OPEN :Temp_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 :Temp_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 :Temp_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 :Temp_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 :Temp_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 Temporary LOB: */ EXEC SQL LOB CLOSE :Temp_loc; /* Free the Temporary LOB: */ EXEC SQL LOB FREE TEMPORARY :Temp_loc; /* Free resources held by the Locator: */ EXEC SQL FREE :Temp_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; writeDataToTempLOB_proc(1); /* Write One Piece */ writeDataToTempLOB_proc(4); /* Write Multiple Pieces using Polling */ EXEC SQL ROLLBACK WORK RELEASE;}
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|