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, 16 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to copy all or part of one temporary LOB to another.
Not applicable.
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:
Assume the following table:
CREATE TABLE VoiceoverLib_tab of VOICED_TYP;
Note that this VoiceoverLib_tab is of the same type as the Voiceover_tab which is referenced by the Voiced_ref column of table Multimedia_tab.
INSERT INTO Voiceover_tab (SELECT * FROM VoiceoverLib_tab Vtab1 WHERE T2.Take = 101);
This creates a new LOB locator in table Voiceover_tab, and copies the LOB data from Vtab1 to the location pointed to by a new LOB locator which is inserted into table Voiceover_tab.
Examples are provided in the following programmatic environments:
/* Note that the example procedure copyTempLOB_proc is not part of the DBMS_LOB package. */ CREATE OR REPLACE PROCEDURE copyTempLOB_proc IS Dest_pos NUMBER; Src_pos NUMBER; Dest_loc BLOB; Dest_loc2 BLOB; Src_loc BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio'); Amount INTEGER := 32767; BEGIN DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE); DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE); /* Opening the FILE is mandatory: */ DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); /* Opening the temporary LOBs is optional: */ DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount); /* Set Dest_pos to the position at which we should start writing in the target temp LOB */ /* Copies the LOB from the source position to the destination position:*/ /* Set amount to the amount you want copied */ Amount := 328; Dest_pos := 1000; Src_pos := 1000; /* Set Src_pos to the position from which we should start copying data from tclob_src: */ DBMS_LOB.COPY(Dest_loc2,Dest_loc, Amount, Dest_pos, Src_pos); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Operation failed'); DBMS_LOB.CLOSE(Dest_loc); DBMS_LOB.CLOSE(Dest_loc2); DBMS_LOB.CLOSE(Src_loc); DBMS_LOB.FREETEMPORARY(Dest_loc); DBMS_LOB.FREETEMPORARY(Dest_loc2); END;
/* This function copies 4000 bytes from one temporary LOB to another. It reads the source LOB starting at offset 1, and writes to the destination at offset 2. The function returns 0 if it completes successfully, and -1 otherwise. */ sb4 copy_temp_lobs (OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { OCIDefine *defnp1; OCILobLocator *tblob; OCILobLocator *tblob2; OCILobLocator *bfile; int rowind =1; ub4 amount=4000; ub4 src_offset=1; ub4 dest_offset=2; sb4 return_code = 0; printf("in copy_temp_lobs \n"); if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0)) { printf("OCIDescriptorAlloc failed in copy_temp_lobs\n"); return -1; } if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&bfile, (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid**)0)) { printf("OCIDescriptorAlloc failed in copy_temp_lobs\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(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob2, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0)) { printf("OCIDescriptorAlloc failed in copy_temp_lobs\n"); return_code = -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_code = -1; } if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR", (ub2)strlen("AUDIO_DIR"), (text *)"Washington_audio", (ub2)strlen("Washington_audio"))) { printf("OCILobFileSetName FAILED\n"); return_code = -1; } if(OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY)) { printf( "OCILobFileOpen FAILED for the bfile\n"); return_code = -1; } if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE)) { printf( "OCILobOpen FAILED for temp LOB \n"); return_code = -1; } if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob2, OCI_LOB_READWRITE )) { printf( "OCILobOpen FAILED for temp LOB \n"); return_code = -1; } if(OCILobLoadFromFile(svchp, errhp, tblob, (OCILobLocator*)bfile, (ub4)amount, (ub4)1,(ub4)1)) { printf( "OCILobLoadFromFile FAILED\n"); return_code = -1; } if (OCILobCopy(svchp, errhp, tblob2, tblob, amount, dest_offset, src_offset)) { printf ("FAILED: OCILobCopy in copy_temp_lobs\n"); return -1; } /* Close LOBs here */ if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile)) { printf( "OCILobFileClose FAILED for bfile \n"); return_code = -1; } if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob)) { printf( "OCILobClose FAILED for temporary LOB \n"); return_code = -1; } if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob2)) { printf( "OCILobClose FAILED for temporary LOB \n"); return_code = -1; } /* free the temporary lobs now that we are done using them */ if(OCILobFreeTemporary(svchp, errhp, tblob)) { printf("OCILobFreeTemporary FAILED \n"); return_code = -1; } if(OCILobFreeTemporary(svchp, errhp, tblob2)) { printf("OCILobFreeTemporary FAILED \n"); return_code = -1; } return return_code; }
IDENTIFICATION DIVISION. PROGRAM-ID. TEMP-BLOB-COPY. 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. * Define the source and destination position and location: 01 SRC-POS PIC S9(9) COMP VALUE 1. 01 DEST-POS PIC S9(9) COMP VALUE 1. 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. 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. * Copy data from BFILE to temporary LOB: EXEC SQL LOB COPY :AMT FROM :TEMP-SRC AT :SRC-POS TO :TEMP-DEST AT :DEST-POS 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 copyTempLOB_proc() { OCIBlobLocator *Temp_loc1, *Temp_loc2; OCIBFileLocator *Lob_loc; char *Dir = "AUDIO_DIR", *Name = "Washington_audio"; int Amount; 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 one of the Temporary LOBs: */ Amount = 4096; EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1; /* Copy a specified amount from one Temporary LOB to another: */ EXEC SQL LOB COPY :Amount FROM :Temp_loc1 TO :Temp_loc2; /* Closing the LOBs is Mandatory if they have been Opened: */ EXEC SQL LOB CLOSE :Temp_loc1; EXEC SQL LOB CLOSE :Temp_loc2; EXEC SQL LOB CLOSE :Lob_loc; /* 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 :Temp_loc1; EXEC SQL FREE :Temp_loc2; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; copyTempLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|