Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Unanswered: Reading a column of type BFILE

    I found this script on one of the oracle sites but I am getting an error that I don't understand... maybe someone could help...

    1. Is it that the column is NULL (empty)

    or

    2. Is data there but it 'points' to a location that is not valid? If so, how can I find out what that data is?

    Thanks in advance,

    John

    +++++++++++

    SQL> DECLARE
    2 fil BFILE;
    3 pos INTEGER;
    4 amt BINARY_INTEGER;
    5 buf RAW(40);
    6 BEGIN
    7 SELECT BFILE_ID INTO fil FROM mytable WHERE MY_ID = '21';
    8 dbms_lob.open(fil, dbms_lob.lob_readonly);
    9 amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
    10 dbms_lob.read(fil, amt, pos, buf);
    11 dbms_output.put_line('Read F1 past EOF: '||
    12 utl_raw.cast_to_varchar2(buf));
    13 dbms_lob.close(fil);
    14 exception
    15 WHEN no_data_found
    16 THEN
    17 BEGIN
    18 dbms_output.put_line('End of File reached. Closing file');
    19 dbms_lob.fileclose(fil);
    20 -- or dbms_lob.filecloseall if appropriate
    21 END;
    22 END;
    23 /
    DECLARE
    *
    ERROR at line 1:
    ORA-22285: non-existent directory or file for FILEOPEN operation
    ORA-06512: at "SYS.DBMS_LOB", line 605
    ORA-06512: at line 8

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I've worked a little with BFILE's, so I'm not 100% sure, but the error code you listed usually points to errors in how you reference the DIRECTORY object. When you store the LOB, even though you specify the directory name as lower case, it is stored as upper case. When you retrieve it, make sure it is passed as upper.

    Hope this helps!

    JoeB
    Last edited by joebednarz; 07-30-04 at 16:19.

Posting Permissions

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