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, 17 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to copy a LOB locator for a temporary LOB.
Not applicable.
Use the following syntax references for each programmatic environment:
This generic operation copies one temporary LOB locator to another.
Examples are provided in the following programmatic environments:
|
Note: Assigning one LOB to another using PL/SQL entails using the "=" sign. This is discussed in more detail with regard to "Read-Consistent Locators" in Chapter 5, "Advanced Topics". |
/* Note that the example procedure copyTempLOBLocator_proc is not part of the DBMS_LOB package. */ CREATE OR REPLACE PROCEDURE copyTempLOBLocator_proc( Lob_loc1 IN OUT CLOB, Lob_loc2 IN OUT CLOB) IS bufp VARCHAR2(4); Amount NUMBER := 32767; Src_loc BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio'); BEGIN DBMS_LOB.CREATETEMPORARY(Lob_loc1,TRUE); DBMS_LOB.CREATETEMPORARY(Lob_loc2,TRUE); /* Populate the first temporary LOB with some data. */ /* Opening file is mandatory: */ DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READONLY); /* Opening LOB is optional: */ DBMS_LOB.OPEN(Lob_loc1,DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(Lob_loc2,DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADFROMFILE(Lob_loc1,Src_loc,Amount); /* Assign Lob_loc1 to Lob_loc2 thereby creating a copy of the value of the temporary LOB referenced by Lob_loc1 at this point in time: */ Lob_loc2 := Lob_loc1; /* When you write some data to the LOB through Lob_loc1, Lob_loc2 will not see the newly written data whereas Lob_loc1 will see the new data: */ /*Closing LOBs is mandatory if they were opened: */ DBMS_LOB.CLOSE (Src_loc); DBMS_LOB.CLOSE (Lob_loc1); DBMS_LOB.CLOSE (Lob_loc2); DBMS_LOB.FREETEMPORARY(Lob_loc1); DBMS_LOB.FREETEMPORARY(Lob_loc2); END;
* This function creates two temporary lobs. It populates one and then copies the locator of that one to the other temporary LOB locator: */ sb4 copy_locators( OCIError *errhp, OCISvcCtx *svchp, OCIEnv *envhp) { sb4 return_code = 0; OCILobLocator *tblob; OCILobLocator *tblob2; OCILobLocator *bfile; ub4 amount = 4000; checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob, (ub4) OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)); checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob2, (ub4) OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)); checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0)); 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; } if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY)) { printf( "OCILobFileOpen FAILED for the bfile load_temp \n"); return -1; } 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(OCILobCreateTemporary(svchp,errhp, tblob2,(ub2)0, SQLCS_IMPLICIT, OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)) { (void) printf("FAILED: CreateTemporary() \n"); return -1; } if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE)) { printf( "OCILobOpen FAILED for temp LOB \n"); return -1; } if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob2, 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_code = -1; } if(OCILobLocatorAssign(svchp,errhp, (CONST OCILobLocator *)tblob,&tblob2)) { printf("OCILobLocatorAssign failed \n"); return_code = -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))); checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob2))); /* Free the temporary lobs now that we are done using it */ if(OCILobFreeTemporary(svchp, errhp, tblob)) { printf("OCILobFreeTemporary FAILED \n"); return -1; } if(OCILobFreeTemporary(svchp, errhp, tblob2)) { printf("OCILobFreeTemporary FAILED \n"); return -1; } }
IDENTIFICATION DIVISION. PROGRAM-ID. TEMP-BLOB-COPY-LOCATOR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 TEMP-DEST SQL-BLOB. 01 TEMP-SRC SQL-BLOB. 01 SRC-BFILE SQL-BFILE. 01 DIR-ALIAS PIC X(30) VARYING. 01 FNAME PIC X(30) VARYING. 01 AMT PIC S9(9) COMP. 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. TEMP-BLOB-COPY-LOCATOR. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. EXEC SQL CONNECT :USERID END-EXEC. * Allocate and initialize the BLOB locators: EXEC SQL ALLOCATE :TEMP-DEST END-EXEC. EXEC SQL ALLOCATE :TEMP-SRC END-EXEC. EXEC SQL ALLOCATE :SRC-BFILE END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-DEST END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-SRC 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-SRC READ WRITE END-EXEC. EXEC SQL LOB OPEN :TEMP-DEST READ WRITE END-EXEC. EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. * MOVE the desired amount to copy to AMT: MOVE 5 TO AMT. EXEC SQL LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-SRC END-EXEC. * Assign source BLOB locator to destination BLOB locator: EXEC SQL LOB ASSIGN :TEMP-SRC TO :TEMP-DEST END-EXEC. EXEC SQL LOB CLOSE :TEMP-SRC END-EXEC. EXEC SQL LOB CLOSE :TEMP-DEST END-EXEC. EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. EXEC SQL LOB FREE TEMPORARY :TEMP-SRC END-EXEC. EXEC SQL LOB FREE TEMPORARY :TEMP-DEST END-EXEC. EXEC SQL FREE :TEMP-SRC END-EXEC. EXEC SQL FREE :TEMP-DEST 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.
#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 copyTempLobLocator_proc() { OCIBlobLocator *Temp_loc1, *Temp_loc2; 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 LOBs: */ EXEC SQL ALLOCATE :Temp_loc1; EXEC SQL ALLOCATE :Temp_loc2; EXEC SQL LOB CREATE TEMPORARY :Temp_loc1; EXEC SQL LOB CREATE TEMPORARY :Temp_loc2; /* Allocate and Initialize the BFILE Locator: */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name; /* Opening the LOBs is Optional: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; EXEC SQL LOB OPEN :Temp_loc1 READ WRITE; EXEC SQL LOB OPEN :Temp_loc2 READ WRITE; /* Load a specified amount from the BFILE into the Temporary LOB: */ EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1; /* Assign Temp_loc1 to Temp_loc2 thereby creating a copy of the value of the Temporary LOB referenced by Temp_loc1 at this point in time: */ EXEC SQL LOB ASSIGN :Temp_loc1 TO :Temp_loc2; /* Closing the LOBs is Mandatory if they have been Opened: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL LOB CLOSE :Temp_loc1; EXEC SQL LOB CLOSE :Temp_loc2; /* Free the Temporary LOBs: */ EXEC SQL LOB FREE TEMPORARY :Temp_loc1; EXEC SQL LOB FREE TEMPORARY :Temp_loc2; /* Release resources held by the Locators: */ EXEC SQL FREE :Lob_loc; EXEC SQL FREE :Temp_loc1; EXEC SQL FREE :Temp_loc2; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; copyTempLobLocator_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|