Release 1 (9.0.1)
Part Number A88879-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
External LOBs (BFILEs), 24 of 41
|
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to read portion of BFILE data (substr).
Not applicable.
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 following examples read an audio recording into RECORDING from BFILE 'AUDIO_DIR'.
Examples are provided in these five programmatic environments:
/* Note that the example procedure substringBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE substringBFILE_proc IS File_loc BFILE; Position INTEGER := 1; Buffer RAW(32767); BEGIN /* Select the LOB: */ SELECT Mtab.Voiced_ref.Recording INTO File_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* Open the BFILE: */ DBMS_LOB.OPEN(File_loc, DBMS_LOB.LOB_READONLY); Buffer := DBMS_LOB.SUBSTR(File_loc, 255, Position); /* Close the BFILE: */ DBMS_LOB.CLOSE(File_loc); END;
This script is also provided in:
$ORACLE_HOME/rdbms/demo/lobs/cobol/freadprt
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-SUBSTR. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 BFILE1 SQL-BFILE. 01 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 POS PIC S9(9) COMP VALUE 1024. 01 OFFSET PIC S9(9) COMP VALUE 1. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL VAR BUFFER2 IS VARRAW(32767) END-EXEC. PROCEDURE DIVISION. BFILE-SUBSTR. * Allocate and initialize the CLOB locator: EXEC SQL ALLOCATE :BFILE1 END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT MTAB.VOICED_REF.RECORDING INTO :BFILE1 FROM MULTIMEDIA_TAB MTAB WHERE MTAB.CLIP_ID = 3 END-EXEC. * Open the BFILE for READ ONLY: EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC. * Execute PL/SQL to use its SUBSTR functionality: MOVE 32767 TO AMT. EXEC SQL EXECUTE BEGIN :BUFFER2 := DBMS_LOB.SUBSTR(:BFILE1,:AMT,:POS); END; END-EXEC. * Close and free the locators: EXEC SQL LOB CLOSE :BFILE1 END-EXEC. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 END-EXEC.
This script is also provided in:
$ORACLE_HOME/rdbms/demo/lobs/proc/freadprt
/* Pro*C/C++ lacks an equivalent embedded SQL form for the DBMS_LOB.SUBSTR() function. However, Pro*C/C++ can interoperate with PL/SQL using anonymous PL/SQL blocks embedded in a Pro*C/C++ program as this example shows: */ #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); } #define BufferLength 256 void substringBFILE_proc() { OCIBFileLocator *Lob_loc; int Position = 1; char Buffer[BufferLength]; EXEC SQL VAR Buffer IS RAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); EXEC SQL ALLOCATE :Lob_loc; EXEC SQL SELECT Mtab.Voiced_ref.Recording INTO :Lob_loc FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3; /* Open the BFILE: */ EXEC SQL LOB OPEN :Lob_loc READ ONLY; /* Invoke SUBSTR() from within an anonymous PL/SQL block: */ EXEC SQL EXECUTE BEGIN :Buffer := DBMS_LOB.SUBSTR(:Lob_loc, 256, :Position); END; END-EXEC; /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; substringBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
Dim MySession As OraSession Dim OraDb As OraDatabase Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) chunk_size = 32767 Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) Set OraMusic = OraDyn.Fields("Music").Value OraMusic.PollingAmount = OraMusic.Size 'Read entire BFILE contents OraMusic.offset = 255 'Read from the 255th position 'Open the Bfile for reading: OraMusic.Open amount_read = OraMusic.Read(chunk, chunk_size) 'chunk returned is a variant of type byte array If amount_read <> chunk_size Then 'Do error processing Else 'Process the data End If
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 Ex4_62 { 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; ResultSet rset = null; InputStream in = null; byte buf[] = new byte[1000]; int length = 0; rset = stmt.executeQuery ( "SELECT music FROM multimedia_tab WHERE clip_id = 2"); if (rset.next()) { src_lob = ((OracleResultSet)rset).getBFILE (1); } // Open the BFILE: src_lob.openFile(); // Get a handle to stream the data from the BFILE in = src_lob.getBinaryStream(); if (in != null) { // request 255 bytes into buf, starting from offset 1. // length = # bytes actually returned from stream: length = in.read(buf, 1, 255); System.out.println("Bytes read in: " + Integer.toString(length)); // Process the buf: System.out.println(new String(buf)); } // Close the stream, BFILE, statement and connection: in.close(); src_lob.closeFile(); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|