Mapping Oracle Physical File with Logical Directory
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Mapping Oracle Physical File with Logical Directory

  1. #1
    Join Date
    Aug 2002
    Posts
    40

    Arrow Mapping Oracle Physical File with Logical Directory

    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'??
    HOW TO MAP PHYSICAL DIRECTORY CONTAINING "P2.DOC" WITH LOGICAL DIECTORY 'D:\PROPOSAL\LETTERS' WHICH I CREATED BY RUNNING-
    CREATE DIRECTORY PROPOSAL_DIR AS 'D:\PROPOSAL\LETTERS'
    please suggest.
    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Have a physical dir as D:\PROPOSAL\LETTERS..put p2.doc in the letters Dir...

    The Logical Dir PROPOSAL_DIR would be pointing ur Physical Dir D:\PROPOSAL\LETTERS ( If u look at OS point of view it wud be again a Logical Loc ).

    'path_name'
    is the full pathname of the operating system directory on the server where the files are located. The single quotes are required, with the result that the path name is case sensitive.
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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