Retrieving Word Document..

[rameshbabumande]

Dear members,

I stored a document in BLOB , I want to retrieve it from the database and store it in the hard disk . How can I retrieve the document using pl/sql , I could not use utl_file package becoz its a binary file . I hope I am correct regarding utl_file usage.
Do help me out .

Ramesh.M

[Tarry]

I knew this would be the next question.
What exactly do you mean by retrieving, retrive through the sqlplus screen?

There are 6(could be more) programmtic env to do the insert and read...
pl/sql (dbms_lob)
c(oci)
c/c++(pro*C)
cobol(pro*cobol)
vb(oo4o)
java(jdbc)

Since you're using pl/sql...

If your retrieve is "reading the data"...

CREATE OR REPLACE PROCEDURE read_prc IS
   Lob_loc       BFILE := BFILENAME('your_dir', 'your_doc');
   Amount        INTEGER := 32767;
   Position      INTEGER := 1;
   Buffer        RAW(32767);
BEGIN
   /* Select the LOB: */  
   SELECT mydoc INTO Lob_loc FROM worddoc_table 
      WHERE doc_id = 1;
   /* Open the BFILE: */  
   DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Read data: */  
   DBMS_LOB.READ(Lob_loc, Amount, Position, Buffer);
   /* Close the BFILE: */  
   DBMS_LOB.CLOSE(Lob_loc);
END;

If retrieving means displaying...

CREATE OR REPLACE PROCEDURE displayBFILE_proc IS 
   Lob_loc  BFILE; 
   Buffer   RAW(1024); 
   Amount   BINARY_INTEGER := 1024; 
   Position INTEGER        := 1; 
BEGIN 
   /* Select the LOB: */ 
   SELECT mydoc INTO Lob_loc 
   FROM worddoc_table WHERE doc_id = 1; 
   /* Opening the BFILE: */ 
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY); 
   LOOP 
      DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer); 
      /* Display the buffer contents: */ 
      DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer)); 
      Position := Position + Amount; 
   END LOOP; 
   /* Closing the BFILE: */ 
   DBMS_LOB.CLOSE (Lob_loc); 
   EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('End of data'); 
END;

I haven't tried it(so don't just do copy and paste)you might want to adjust it...

but using jdbc stream you can also store the contents into another os file, I'll have to try it on my box first...

[Tarry]

Hang on, 8i(as i saw in the other post), you'll need to use other 3l language like java/c etc... try this jdbc code for instance....

this insert stores a reference to your operating system file that's in your bfile dir...

// variables
Statement stmt = null;
ResultSet resSet = null;
try {
  // create a statement
  stmt = dbConn.createStatement();
  // insert new row
  stmt.execute(" INSERT INTO lobsamp "
             + " (id"
             + " ,name"
             + " ,bfile_col"
             + " )"
             + " SELECT lobsamp_seq.nextval"
             + " ,      '" + strFile + "'"
             + " ,      bfilename('BFILE_DIR','" + strFile + "')"
             + " FROM   dual");
} catch (Exception ex) {
  System.err.println("Error loading BFILE data: " + ex.toString());
  try { dbConn.rollback(); } catch (Exception e) {}
} finally {
  // close the statement
  try { stmt.close(); } catch (Exception e) {}
}

and this reads the contents in your bfile and stores it into another os file,(all via the database) like you asked.. but test it first!!

// variables
String strStatement = null;
Statement stmt = null;
ResultSet resSet = null;
BFILE bFile = null;
String strFileName = null;
String strDirectory = null;
InputStream bfileInputStream = null;
OutputStream sampleFileStream = null;
try {
  // select the bfile locator from the database
  strStatement = " SELECT name"
               + " ,      bfile_col"
               + " FROM   lobsamp"
               + " WHERE  id = " + lngId;
  stmt = dbConn.createStatement();
  resSet = stmt.executeQuery(strStatement);
  if (resSet.next()) {
    strFileName = resSet.getString("name");
    // get the locator
    bFile = ((OracleResultSet)resSet).getBFILE("bfile_col");
  }
  // Save as dialog box to get directory and file name to save as
  FileDialog selFile = new FileDialog(mainFrame,
  "Save as", FileDialog.SAVE);
  selFile.setFile(strFileName);
  selFile.show();
  strDirectory = selFile.getDirectory();
  strFileName = selFile.getFile();
  // show directory alias
  System.out.println("... directory alias: "
                    + bLocator.getDirAlias() + " ...");
  // show file name
  System.out.println("... file name: "
                    + bLocator.getName() + " ...");
  // check if file exists
  System.out.println("... does the file exist: "
                    + bLocator.fileExists() + " ...");
  // show file length
  System.out.println("... file length = "
                    + bLocator.length() + " ...");
  // open the file and get the stream
  bLocator.openFile();
  bfileInputStream = bLocator.getBinaryStream();
  // open the file to write to
  File file2Save = new File(strDirectory + strFile);
  sampleFileStream = new FileOutputStream(file2Save);
  // define the buffer size
  int intBufferSize = BFILEBUFSIZE*NUMCHUNKS;
  // buffer to hold chunks of data to read from the BFILE
  byte[] bBuffer = new byte[intBufferSize];
  // read a chunk of data from the BFILE, and write
  // the chunk to the file.
  int intBytesRead = 0;
  while ((intBytesRead = bfileInputStream.read(bBuffer)) != -1) {
    sampleFileStream.write(bBuffer,0,intBytesRead);
  }
} catch (Exception ex) {
  System.err.println("Saving BFILE data: " + ex.toString());
} finally {
  // close input stream, output stream, bfile and statement
  if (sampleFileStream != null) {
    try { sampleFileStream.close(); } catch (Exception e) {
      System.err.println("Could not close file output stream!"); }
  }
  if (bfileInputStream != null) {
    try { bfileInputStream.close(); } catch (Exception e) {
      System.err.println("Could not close bfile input stream!"); }
  }
  try { bLocator.closeFile(); } catch (Exception e) {
    System.err.println("Could not close bfile file!"); }
  try { stmt.close(); } catch (Exception e) {
    System.err.println("Could not close the statement!"); }
}

Try it out and let us know here...

[rameshbabumande]

Dear Tarry ,

I thank you for spending ur time in giving ur valuable suggestions to me . I will be working on the concepts which you have discussed , I will let you know that I am able to save the doc in the drive or not .Once again thank you for ur help...

Ramesh.M