Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)

Part Number A88879-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

External LOBs (BFILEs), 32 of 41


Getting DIRECTORY Alias and Filename

Figure 12-28 Use Case Diagram: Get DIRECTORY Alias and Filename


Text description of adl12b26.gif follows This link takes you back to the External LOBs (BFILES) main diagram.
Text description of the illustration adl12b26.gif

See Also:

"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). 

Purpose

This procedure describes how to get DIRECTORY alias and filename.

Usage Notes

Not applicable.

Syntax

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:

Scenario

This example retrieves the DIRECTORY alias and filename related to the BFILE, Music.

Examples

The examples are provided in the following six programmatic environments:

PL/SQL (DBMS_LOB Package): Getting Directory Alias and Filename

CREATE OR REPLACE PROCEDURE getNameBFILE_proc IS
   File_loc         BFILE;
   DirAlias_name   VARCHAR2(30);
   File_name       VARCHAR2(40);
BEGIN
   SELECT Music INTO File_loc FROM Multimedia_tab WHERE Clip_ID = 3;
   DBMS_LOB.FILEGETNAME(File_loc, DirAlias_name, File_name);
   /* do some processing based on the directory alias and file names */
END;

C (OCI): Getting Directory Alias and Filename

/* 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 BfileGetDirFile(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   OraText dir_alias[32] = NULL;
   OraText filename[256]  = NULL;
   ub2 d_length = 32;
   ub2 f_length = 256;
 
   /* Allocate the locator descriptors */ 
   (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, OCILobFileGetName(envhp, errhp, bfile_loc,
                       dir_alias, &d_length, filename, &f_length));

   printf("Directory Alias : [%s]\n", dir_alias);
   printf("File name : [%s]\n", filename);

   /* Free the locator descriptor */ 
   OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Getting Directory Alias and Filename

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/cobol/fgetdir

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-DIR-ALIAS.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.
       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-DIR-ALIAS.

           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.

      * Populate the BFILE locator: 
           EXEC SQL 
                SELECT PHOTO INTO :BFILE1
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC.

      * Use the LOB DESCRIBE functionality to get 
      * the directory alias and the filename: 
           EXEC SQL LOB DESCRIBE :BFILE1 
                GET DIRECTORY, FILENAME INTO :DIR-ALIAS, :FNAME END-EXEC.
      
           DISPLAY "DIRECTORY: ", DIR-ALIAS-ARR, "FNAME: ", FNAME-ARR.
       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.

C/C++ (Pro*C/C++): Getting Directory Alias and Filename

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/proc/fgetdir

#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 getBFILEDirectoryAndFilename_proc()
{
  OCIBFileLocator *Lob_loc;
  char Directory[31], Filename[255];
  /* Datatype Equivalencing is Optional: */
  EXEC SQL VAR Directory IS STRING;
  EXEC SQL VAR Filename IS STRING;
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;

  /* Select the BFILE: */
  EXEC SQL SELECT Photo INTO :Lob_loc
     FROM Multimedia_tab WHERE Clip_ID = 3;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Get the Directory Alias and Filename: */
  EXEC SQL LOB DESCRIBE :Lob_loc
     GET DIRECTORY, FILENAME INTO :Directory, :Filename;

  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  printf("Directory Alias: %s\n", Directory);
  printf("Filename: %s\n", Filename);
  /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  getBFILEDirectoryAndFilename_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Visual Basic (OO4O): Getting Directory Alias and Filename

'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
Dim OraMusic1 As OraBfile, OraSql As OraSqlStmt

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 OraMusic1 = OraParameters("MyMusic").Value
'Get Directory alias and filename: 
MsgBox " Directory alias is " & OraMusic1.DirectoryName & 
   " Filename is " & OraMusic1.filename

OraDb.Connection.CommitTrans

Java (JDBC): Getting Directory Alias and Filename

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();
    }
  }
}


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback