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), 28 of 41
|
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to get the length of a BFILE.
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:
This example gets the length of a BFILE that is associated with Recording.
The examples are provided in six programmatic environments:
/* Note that the example procedure getLengthBFILE_proc is not part of the DBMS_LOB package: */ CREATE OR REPLACE PROCEDURE getLengthBFILE_proc IS File_loc BFILE; Length INTEGER; BEGIN /* Initialize the BFILE locator by selecting 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); /* Get the length of the LOB: */ Length := DBMS_LOB.GETLENGTH(File_loc); IF Length IS NULL THEN DBMS_OUTPUT.PUT_LINE('BFILE is null.'); ELSE DBMS_OUTPUT.PUT_LINE('The length is ' || length); END IF; /* Close the BFILE: */ DBMS_LOB.CLOSE(File_loc); END;
/* Select the lob/bfile from the Multimedia table */ void selectLob(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCIDefine *dfnhp; text *selstmt = (text *) "SELECT Photo FROM Multimedia_tab \ WHERE Clip_ID = 3"; /* Prepare the SQL select statement */ checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt, (ub4) strlen((char *) selstmt), (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); /* Call define for the bfile column */ checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1, (dvoid *)&Lob_loc, 0 , SQLT_BFILE, (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 BfileLength(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *bfile_loc; ub4 len; /* Allocate the locator descriptor */ (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0); /* Select the bfile */ selectLob(bfile_loc, errhp, svchp, stmthp); checkerr (errhp, OCILobFileOpen(svchp, errhp, bfile_loc, (ub1) OCI_FILE_READONLY)); checkerr (errhp, OCILobGetLength(svchp, errhp, bfile_loc, &len)); printf("Length of bfile = %d\n", len); checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc)); /* Free the locator descriptor */ OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); }
This script is also provided in:
$ORACLE_HOME/rdbms/demo/lobs/cobol/flength
IDENTIFICATION DIVISION. PROGRAM-ID. BFILE-LENGTH. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(11) VALUES "SAMP/SAMP". 01 BFILE1 SQL-BFILE. 01 LEN PIC S9(9) COMP. 01 D-LEN PIC 9(4). 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. BFILE-LENGTH. 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. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC. EXEC SQL SELECT PHOTO INTO :BFILE1 FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC. * Use LOB DESCRIBE to get length of lob: EXEC SQL LOB DESCRIBE :BFILE1 GET LENGTH INTO :LEN END-EXEC. MOVE LEN TO D-LEN. DISPLAY "Length of BFILE is ", D-LEN. END-OF-BFILE. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL FREE :BFILE1 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/flength
#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 getLengthBFILE_proc() { OCIBFileLocator *Lob_loc; unsigned int Length = 0; 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; /* Get the Length: */ EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Length; /* If the BFILE is NULL or unitialized, then Length is Undefined: */ printf("Length is %d bytes\n", Length); /* Close the BFILE: */ EXEC SQL LOB CLOSE :Lob_loc; EXEC SQL FREE :Lob_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; getLengthBFILE_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
'Note that the PL/SQL packages and the tables mentioned here are not part of the ' 'standard OO4O installation: Dim MySession As OraSession Dim OraDb As OraDatabase Set MySession = CreateObject("OracleInProcServer.XOraSession") Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&) OraDb.Connection.BeginTrans Set OraParameters = OraDb.Parameters OraParameters.Add "id", 1001, ORAPARM_INPUT 'Define out parameter of BFILE type: OraParameters.Add "MyMusic", Null, ORAPARM_OUTPUT OraParameters("MyMusic").ServerType = ORATYPE_BFILE Set OraSql = OraDb.CreateSql( "BEGIN SELECT music INTO :MyMusic FROM multimedia_tab WHERE clip_id = :id; END;", ORASQL_FAILEXEC) Set OraMusic = OraParameters("MyMusic").Value If OraMusic.Size = 0 Then MsgBox "BFile size is 0" Else MsgBox "BFile size is " & OraMusic.Size End If OraDb.Connection.CommitTrans
import java.io.InputStream; import java.io.OutputStream; // Core JDBC classes: import java.sql.DriverManager; import java.sql.Connection; import java.sql.Types; 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_74 { static final int MAXBUFSIZE = 32767; 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"); // It's faster when auto commit is off: conn.setAutoCommit (false); // Create a Statement: Statement stmt = conn.createStatement (); try { BFILE lob_loc = null; ResultSet rset = stmt.executeQuery ( "SELECT photo FROM multimedia_tab WHERE clip_id = 3"); if (rset.next()) { lob_loc = ((OracleResultSet)rset).getBFILE (1); } // See if the BFILE exists: System.out.println("Result from fileExists(): " + lob_loc.fileExists()); // Return the length of the BFILE: long length = lob_loc.length(); System.out.println("Length of BFILE: " + length); // Get the directory alias for this BFILE: System.out.println("Directory alias: " + lob_loc.getDirAlias()); // Get the file name for this BFILE: System.out.println("File name: " + lob_loc.getName()); stmt.close(); conn.commit(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|