Retrieving Word Document..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Retrieving Word Document..

  1. #1
    Join Date
    May 2003
    Posts
    16

    Retrieving Word Document..

    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

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    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"...
    Code:
    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...

    Code:
    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...
    Last edited by Tarry; 05-20-2003 at 08:40 AM.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    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...

    Code:
    // 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!!

    Code:
    // 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...
    Last edited by Tarry; 05-20-2003 at 08:57 AM.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    May 2003
    Posts
    16
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width