Lobs

[bonny]

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

[Srinivas_Sharma]

Hi,

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

SS

[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

[bonny]

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