Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
External LOBs (BFILEs), 15 of 41
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to load a LOB with BFILE data.
In using OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.
When you use the DBMS_LOB.LOADFROMFILE
procedure to populate a CLOB
or NCLOB
, you are populating the LOB
with binary data from the BFILE
. No implicit translation is performed from binary data to a character set.
Hence, when loading data into a CLOB
or NCLOB
from a BFILE
ensure the following for the BFILE
data before you use loadfromfile
:
BFILE
data is in the same character set as the CLOB
or NCLOB
data already in the database, in other words, it is in the char/nchar character set
BFILE
data is encoded in the correct endian format of the server machine
Oracle9i Globalization and National Language Support Guide, for character set conversion issues.
See Also:
amount
parameter to be larger than the size of the BFILE.
amount
parameter to be larger than the length of the BFILE.
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:
These example procedures assume there is a directory object (AUDIO_DIR
) that contains the LOB
data to be loaded into the target LOB
(Music
). Examples are provided in the following six programmatic environments:
/* Note that the example procedure loadLOBFromBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS Dest_loc BLOB; Src_loc BFILE := BFILENAME('FRAME_DIR', 'Washington_frame'); Amount INTEGER := 4000; BEGIN SELECT Frame INTO Dest_loc FROM Multimedia_tab WHERE Clip_ID = 3 FOR UPDATE; /* Opening the LOB is mandatory: */ DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); /* Opening the LOB is optional: */ DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE(Dest_loc); DBMS_LOB.CLOSE(Src_loc); COMMIT; END;
/* Insert a row using BFILENAME: */ /* Select the lob/bfile from the Multimedia table */ void selectLob(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { char selstmt[150]; OCIDefine *dfnhp; strcpy(selstmt, (char *) "SELECT FRAME FROM MULTIMEDIA_TAB \ WHERE CLIP_ID=3 FOR UPDATE"); /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Define the column being selected */ checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BLOB, (dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT)); /* Execute the SQL select statement */ checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); } void loadLobFromBfile(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *dest_loc; OCILobLocator *src_loc; /* Allocate locators */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &dest_loc, (ub4)OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0); (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &src_loc, (ub4)OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0); checkerr(errhp, OCILobFileSetName(envhp, errhp, &src_loc, (text *) "PHOTO_DIR", (ub2) strlen("PHOTO_DIR"), (text *) "Lincoln_photo", (ub2) strlen("Lincoln_photo"))); selectLob(dest_loc, errhp, svchp, stmthp); checkerr(errhp, OCILobFileOpen(svchp, errhp, src_loc, (ub1)OCI_FILE_READONLY)); checkerr(errhp, OCILobOpen(svchp, errhp, dest_loc, (ub1)OCI_LOB_READWRITE)); checkerr (errhp, OCILobLoadFromFile(svchp, errhp, dest_loc, src_loc, (ub4)4000, (ub4)1, (ub4)1)); checkerr(errhp, OCILobClose(svchp, errhp, dest_loc)); checkerr(errhp, OCILobFileClose(svchp, errhp, src_loc)); }
This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/fload
IDENTIFICATION DIVISION. PROGRAM-ID. LOAD-BFILE. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 DEST-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. 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-BFILE. * Allocate and initialize the LOB locators: EXEC SQL ALLOCATE :DEST-BLOB END-EXEC. EXEC SQL ALLOCATE :SRC-BFILE 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. * Populate the BFILE: EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT PHOTO INTO :SRC-BFILE FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. * Open the source BFILE READ ONLY. * Open the destination BLOB READ/WRITE: EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. EXEC SQL LOB OPEN :DEST-BLOB READ WRITE END-EXEC. * Load BFILE data into the BLOB: EXEC SQL LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :DEST-BLOB END-EXEC. * Close the LOBs: EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. EXEC SQL LOB CLOSE :DEST-BLOB END-EXEC. * And free the LOB locators: END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :DEST-BLOB END-EXEC. EXEC SQL FREE :SRC-BFILE END-EXEC. EXEC SQL ROLLBACK WORK RELEASE 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 provided in $ORACLE_HOME/rdbms/demo/lobs/proc/fload
#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 loadLOBFromBFILE_proc() { OCIBlobLocator *Dest_loc; OCIBFileLocator *Src_loc; char *Dir = "AUDIO_DIR", *Name = "Washington_audio"; int Amount = 4096; EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Initialize the BFILE Locator: */ EXEC SQL ALLOCATE :Src_loc; EXEC SQL LOB FILE SET :Src_loc DIRECTORY = :Dir, FILENAME = :Name; /* Initialize the BLOB Locator: */ EXEC SQL ALLOCATE :Dest_loc; EXEC SQL SELECT Sound INTO :Dest_loc FROM Multimedia_tab WHERE Clip_ID = 3 FOR UPDATE; /* Opening the BFILE is Mandatory: */ EXEC SQL LOB OPEN :Src_loc READ ONLY; /* Opening the BLOB is Optional: */ EXEC SQL LOB OPEN :Dest_loc READ WRITE; EXEC SQL LOB LOAD :Amount FROM FILE :Src_loc INTO :Dest_loc; /* Closing LOBs and BFILEs is Mandatory if they have been OPENed: */ EXEC SQL LOB CLOSE :Dest_loc; EXEC SQL LOB CLOSE :Src_loc; /* Release resources held by the Locators: */ EXEC SQL FREE :Dest_loc; EXEC SQL FREE :Src_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; loadLOBFromBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
Dim OraDyn as OraDynaset, OraDyn2 as OraDynaset, OraPhoto as OraBFile
Dim OraImage as OraBlob
chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT)
Set OraPhoto = OraDyn.Fields("Photo").Value
Set OraFrame = OraDyn.Fields("Frame").Value
OraDyn.Edit
'Load LOB with data from BFILE:
OraFrame.CopyFromBFile (OraPhoto)
OraDyn.Update
usage: head [-n #] [-#] [filename...] public class // Commit the transaction: conn.commit(); } catch (SQLException e) { e.printStackTrace(); } } }
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|