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

Thread: Lobs

  1. #1
    Join Date
    Aug 2002
    Posts
    40

    Lightbulb Lobs

    I am working on LOBS(large objects)and have problem of reading BFILE Stored on my system-

    created a table-
    1.CREATE TABLE PROPOSAL
    (PROPOSAL_ID NUMBER(10) CONSTRAINT PROPOSAL_PK1 PRIMARY KEY,
    RECIPIENT_NAME VARCHAR2(25),
    PROPOSAL_NAME VARCHAR2(25),
    SHORT_DESCRIPTION VARCHAR2(1000),
    PROPOSAL_TEXT CLOB,
    BUDGET BLOB,
    COVER_LETTER BFILE)

    2.created a directory as under-
    CREATE DIRECTORY PROPOSAL_DIR AS 'D:\PROPOSAL\LETTERS';

    3.inserted data into the table-
    INSERT INTO PROPOSAL VALUES(
    1,'BRAD OHMONT','REBUILD FENCE',NULL,
    EMPTY_CLOB(),EMPTY_BLOB(),
    BFILENAME('PROPOSAL_DIR','P1.DOC'))

    4. Create or replace procedure displaybfile
    Is
    Lob_loc bfile;
    amount integer := 1024;
    Position integer := 1;
    Buffer raw(1024);
    Begin
    Select cover_letter into lob_loc from proposal where
    proposal_id=1;
    Dbms_lob.open(lob_loc,dbms_lob.lob_readonly);
    loop
    Dbms_lob.read(lob_loc,amount,position,buffer);
    dbms_output.put_line(utl_raw.cast_to_varchar2(buffer));
    position := position + amount;
    end loop;
    Dbms_lob.close(lob_loc);
    exception
    when no_data_found then
    dbms_output.put_line('end of data');
    End;

    5.when I execute this procedure there is error-
    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 "SYSTEM.DISPLAYBFILE", line 9

    QUESTION - I want to open my p2.doc which is text document lying @ "d:\p2.doc" on my system.how to put p2.doc into proposal_dir which is logical directory created as 'D:\PROPOSAL\LETTERS'??
    where I am wrong?
    please suggest.
    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi,

    check out if you have the READ permission on the directory.

    SS

  3. #3
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi,

    If you are trying to read P2.doc, then which logical directory is mapped to it..u should have one oracle directory object which maps to the actual location where P2.doc is situated.

    SS

  4. #4
    Join Date
    Aug 2002
    Posts
    40
    Originally posted by Srinivas_Sharma
    Hi,

    If you are trying to read P2.doc, then which logical directory is mapped to it..u should have one oracle directory object which maps to the actual location where P2.doc is situated.

    SS
    how to map logical directory with oracle diretcory??.once it mapped it is ok i can always put p2.doc in oracle directory.
    please suggest.
    thanks

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