2 Replies Latest reply: Oct 4, 2010 1:39 PM by 802704 RSS

    Copying BLOB content to a OS file using BFILE

    802704
      Hey people, I am trying to develop a procedure for copying the contents of a blob to a file on my operating system..But I am facing some problem in the code of procedure....

      desc blob_content

      Name Null? Type
      ----------------------------------------- -------- ----------------------------

      ID NOT NULL NUMBER(38)
      BLOB_COLUMN NOT NULL BLOB
      ------------------------------------------------------------------------------------------------------
      CREATE OR REPLACE DIRECTORY PHOTO_FOLDER AS 'c:\sample_files';
      ------------------------------------------------------------------------------------------------------
      CREATE OR REPLACE PROCEDURE get_photo
      (p_id NUMBER)
      AS
      f_photo BFILE;
      b_photo BLOB;
      buf RAW(32767);
      BEGIN

      -- find where the photo's pointer is located.
      f_photo := bfilename('PHOTO_FOLDER','temp.jpg');
      -- open the photo as write option.
      dbms_lob.open(f_photo, DBMS_LOB.FILE_READWRITE);
      -- load the photo into column photo.

      SELECT blob_column into b_photo
      FROM blob_content
      WHERE id = p_id;

      read(b_photo,dbms_lob.getlength(b_photo),1,buf);
      -- close the photo's pointer.

      write(f_photo,dbms_lob.getlength(b_photo),1,buf);

      dbms_lob.fileclose(f_photo);
      -- Save the loaded photo record.

      COMMIT;

      EXCEPTION
      -- Check for your error messages
      WHEN others THEN
      dbms_output.put_line('*** ERROR *** Check you procedure.');
      END;


      -- My Question is how to open a bfile in write mode and then how to write content of blob into location pointed by the bfile.....

      While creating this procedure the error I get is :
      LINE/COL ERROR

      12/1 PL/SQL: Statement ignored
      12/33 PLS-00302: component 'FILE_READWRITE' must be declared
      19/1 PL/SQL: Statement ignored
      19/1 PLS-00201: identifier 'READ' must be declared
      22/1 PL/SQL: Statement ignored
      22/1 PLS-00201: identifier 'WRITE' must be declared

      Edited by: 799701 on Oct 3, 2010 1:18 PM

      Edited by: 799701 on Oct 3, 2010 1:19 PM
        • 1. Re: Copying BLOB content to a OS file using BFILE
          Mark Williams-Oracle
          Hi,

          I'm not sure you've landed in the correct forum as this doesn't seem like an ODP.NET question. It seems more like a PL/SQL question and that forum is located here:

          PL/SQL

          However, in response to your question:
          799701 wrote:
          -- My Question is how to open a bfile in write mode and then how to write content of blob into location pointed by the bfile.....
          The answer is you don't. As noted in the documentation a bfile is read-only:

          "The BFILE data type enables read-only support of large binary files. You cannot modify or replicate such a file."

          Source, Oracle Database SQL Language Reference:
          http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref197

          You're also using a non-existent constant (DBMS_LOB.FILE_READWRITE), missing the package qualifier on two calls (read and write) and are masking valuable information should an exception be raised with that "when others".

          In any case, you might find that UTL_FILE in combination with a DIRECTORY that points to the same location as the BFILE will be enough to satisfy your requirement. But, this is better suited to the PL/SQL forum in my view.

          As always, corrections, clarifications, etc. welcome.

          - Mark
          • 2. Re: Copying BLOB content to a OS file using BFILE
            802704
            Thank You Mark.....I've posted my question on PL/SQL forum....