Release 2 (8.1.6)
A76940-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Large Objects (LOBs) Release 2 (8.1.6) A76940-01 |
|
Internal Persistent LOBs, 13 of 42
|
See:
"Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs. |
This procedure describes how to load a LOB with data from a BFILE.
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. However, no implicit translation is ever performed from binary data to a character set.
When you use the LOADFROMFILE procedure 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 Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:
The examples assume that there is an operating system source file (Washington_audio) that contains LOB data to be loaded into the target LOB (Music). The examples also assume that directory object AUDIO_DIR already exists and is mapped to the location of the source file.
Examples are provided in the following 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 source BFILE 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;
/* This example illustrates how to select a BLOB from Multimedia_tab and load it with data from a BFILE */
sb4 select_lock_frame_locator_3(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=3 FOR UPDATE"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* Execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return 0; } void LoadLobDataFromBFile(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *bfile; OCILobLocator *blob; ub4 amount= 4000; /* Allocate the Source (bfile) & destination (blob) locators desriptors*/ OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&bfile, (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid **)0); OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&blob, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0); /* Select a frame locator for update */ printf (" select the frame locator...\n"); select_lock_frame_locator_3(blob, errhp, svchp, stmthp); /* Set the Directory Alias and File Name of the frame file */ printf (" set the file name in bfile\n"); checkerr (errhp, OCILobFileSetName(envhp, errhp, &bfile, (text*)"FRAME_DIR", (ub2)strlen("FRAME_DIR"), (text*)"Washington_frame", (ub2)strlen("Washington_frame"))); printf (" open the bfile\n"); /* Opening the BFILE locator is Mandatory */ checkerr (errhp, (OCILobOpen(svchp, errhp, bfile, OCI_LOB_READONLY))); printf(" open the lob\n"); /* Opening the BLOB locator is optional */ checkerr (errhp, (OCILobOpen(svchp, errhp, blob, OCI_LOB_READWRITE))); /* Load the data from the audio file (bfile) into the blob */ printf (" load the LOB from File\n"); checkerr (errhp, OCILobLoadFromFile(svchp, errhp, blob, bfile, (ub4)amount, (ub4)1, (ub4)1)); /* Closing the LOBs is Mandatory if they have been Opened */ checkerr (errhp, OCILobClose(svchp, errhp, bfile)); checkerr (errhp, OCILobClose(svchp, errhp, blob)); /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) bfile, (ub4) OCI_DTYPE_FILE); (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB); return; }
IDENTIFICATION DIVISION.
PROGRAM-ID. LOB-LOAD.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 DEST SQL-BLOB.
01 BFILE1 SQL-BFILE.
01 DIR-ALIAS PIC X(30) VARYING.
01 FNAME PIC X(20) VARYING.
* Declare the amount to load. The value here
* was chosen arbitrarily
01 LOB-AMT PIC S9(9) COMP VALUE 10.
01 USERID PIC X(11) VALUES "SAMP/SAMP".
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
LOB-LOAD.
EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
EXEC SQL CONNECT :USERID END-EXEC.
* Allocate and initialize the BFILE locator
EXEC SQL ALLOCATE :BFILE1 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 :BFILE1 DIRECTORY = :DIR-ALIAS,FILENAME = :FNAME
END-EXEC.
* Allocate and initialize the destination BLOB
EXEC SQL ALLOCATE :DEST END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
EXEC SQL SELECT SOUND INTO :DEST
FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 FOR UPDATE END-EXEC.
* Open the source BFILE for READ
EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.
* Open the destination BLOB for READ/WRITE
EXEC SQL LOB OPEN :DEST READ WRITE END-EXEC.
* Load the destination BLOB from the source BFILE
EXEC SQL LOB LOAD :LOB-AMT FROM FILE :BFILE1 INTO :DEST END-EXEC.
* Close the source and destination LOBs
EXEC SQL LOB CLOSE :BFILE1 END-EXEC.
EXEC SQL LOB CLOSE :DEST END-EXEC.
END-OF-BLOB.
EXEC SQL FREE :DEST END-EXEC.
EXEC SQL FREE :BFILE1 END-EXEC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
SQL-ERROR.
EXEC SQL
WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
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 loadLOBFromBFILE_proc() { OCIBlobLocator *Dest_loc; OCIBFileLocator *Src_loc; char *Dir = "FRAME_DIR", *Name = "Washington_frame"; int Amount = 4000; 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 frame 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, OraSound1 as OraBLOB, OraMyBfile as OraBFile OraConnection.BeginTrans Set OraDyn = OraDb.CreateDynaset( "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT) Set OraSound1 = OraDyn.Fields("Sound").Value OraDb.Parameters.Add "id", 1,ORAPARAM_INPUT OraDb.Parameters.Add "mybfile", Null,ORAPARAM_OUTPUT OraDb.Parameters("mybfile").serverType = ORATYPE_BFILE OraDb.ExecuteSQL ("begin GetBFile(:id, :mybfile); end;") Set OraMyBFile = OraDb.Parameters("mybfile").Value 'Go to Next row OraDyn.MoveNext OraDyn.Edit 'Lets update OraSound1 data with that from the BFILE OraSound1.CopyFromBFile OraMyBFile OraDyn.Update OraConnection.CommitTrans
// Java IO classes: import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // Oracle Specific JDBC classes: import oracle.sql.*; import oracle.jdbc.driver.*; public class Ex2_45 { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver: DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); // Connect to the database: Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp"); conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE src_lob = null; BLOB dest_lob = null; InputStream in = null; OutputStream out = null; byte buf[] = new byte[1000]; ResultSet rset = null; rset = stmt.executeQuery ( "SELECT BFILENAME('AUDIO_DIR', 'Washington_audio') FROM DUAL"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); src_lob.openFile(); in = src_lob.getBinaryStream(); } rset = stmt.executeQuery ( "SELECT sound FROM multimedia_tab WHERE clip_id = 99 FOR UPDATE"); if (rset.next()) { dest_lob = ((OracleResultSet)rset).getBLOB (1); // Fetch the output stream for dest_lob: out = dest_lob.getBinaryOutputStream(); } int length = 0; int pos = 0; while ((in != null) && (out != null) && ((length = in.read(buf)) != -1)) { System.out.println( "Pos = " + Integer.toString(pos) + ". Length = " + Integer.toString(length)); pos += length; out.write(buf, pos, length); } // Close all streams and file handles: in.close(); out.flush(); out.close(); src_lob.closeFile(); // Commit the transaction: conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|