Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Temporary LOBs, 8 of 29
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to load a temporary LOB with data from a BFILE.
In using OCI, or any programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile
operation to populate a CLOB
or NCLOB
, you are populating the LOB
with binary data from the BFILE
. In that case, you will need to perform character set conversions on the BFILE
data before executing loadfromfile
.
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 example procedures assume that there is an operating system source directory (AUDIO_DIR
) that contains the LOB
data to be loaded into the target LOB
.
Examples are provided in the following programmatic environments:
/* Note that the example procedure freeTempLob_proc is not part of the DBMS_LOB package: */ CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS BEGIN DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE); /* Use the temporary LOB locator here, then free it.*/ /* Free the temporary LOB locator: */ DBMS_LOB.FREETEMPORARY(Lob_loc); DBMS_OUTPUT.PUT_LINE('Temporary LOB was freed'); END;
/* Here is a section of code which shows how to create a temporary LOB, and load the contents of a BFILE into the temporary LOB: */ sb4 load_temp(OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { OCILobLocator *bfile; int amount =100; OCILobLocator *tblob; printf("in load_temp\n"); if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0)) { printf("OCIDescriptorAlloc failed in load_temp\n"); return -1; } if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&bfile, (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid**)0)) { printf("OCIDescriptorAlloc failed in load_temp\n"); return -1; } /* Create a temporary LOB: */ if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT, OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)) { (void) printf("FAILED: CreateTemporary() \n"); return -1; } if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR", (ub2)strlen("AUDIO_DIR"), (text *)"Washington_audio", (ub2)strlen("Washington_audio"))) { printf("OCILobFileSetName FAILED in load_temp\n"); return -1; } /* Opening the BFILE is mandatory: */ if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY)) { printf( "OCILobFileOpen FAILED for the bfile load_temp \n"); return -1; } /* Opening the LOB is optional: */ if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE)) { printf( "OCILobOpen FAILED for temp LOB \n"); return -1; } if(OCILobLoadFromFile(svchp, errhp, tblob, (OCILobLocator*)bfile, (ub4)amount, (ub4)1,(ub4)1)) { printf( "OCILobLoadFromFile FAILED\n"); return -1; } /* Close the lobs: */ if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile)) { printf( "OCILobClose FAILED for bfile \n"); return -1; } checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob))); /* Free the temporary LOB now that we are done using it */ if(OCILobFreeTemporary(svchp, errhp, tblob)) { printf("OCILobFreeTemporary FAILED \n"); return -1; } }
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/tloadbf
IDENTIFICATION DIVISION. PROGRAM-ID. LOAD-TEMPORARY. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "USER1/USER1". 01 TEMP-BLOB SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 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. LOAD-TEMPORARY. 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 :SRC-BFILE END-EXEC. EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. * Set up the directory and file information: MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR. MOVE 9 TO DIR-ALIAS-LEN. MOVE "washington_audio" TO FNAME-ARR. MOVE 16 TO FNAME-LEN. EXEC SQL LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS, FILENAME = :FNAME END-EXEC. * Open source BFILE and destination temporary BLOB: EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC. EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. EXEC SQL LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB END-EXEC. * Close the LOBs: EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC. * Free the temporary LOB: EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC. * And free the LOB locators: EXEC SQL FREE :TEMP-BLOB END-EXEC. EXEC SQL FREE :SRC-BFILE 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 located at $ORACLE_HOME/rdbms/demo/lobs/proc/tload
#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); } void loadTempLobFromBFILE_proc() { OCIBlobLocator *Temp_loc; OCIBFileLocator *Lob_loc; char *Dir = "AUDIO_DIR", *Name = "Washington_audio"; int Amount = 4096; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Create the Temporary LOB: */ EXEC SQL ALLOCATE :Temp_loc; EXEC SQL LOB CREATE TEMPORARY :Temp_loc; /* Allocate and Initialize the BFILE Locator: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; /* Opening the BFILE is mandatory; */ /* Opening the LOB is optional: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; EXEC SQL LOB OPEN :Temp_loc READ WRITE; /* Load the data from the BFILE into the Temporary LOB: */ EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc; /* Closing the LOBs is Mandatory if they have been Opened: */ EXEC SQL LOB CLOSE :Temp_loc; EXEC SQL LOB CLOSE :Lob_loc; /* Free the Temporary LOB: */ EXEC SQL LOB FREE TEMPORARY :Temp_loc; /* Release resources held by the Locators: */ EXEC SQL FREE :Temp_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; loadTempLobFromBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|