Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Oracle Directories
Problem with Oracle Directories [message #285774] Wed, 05 December 2007 10:12 Go to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
Hi All,

I am trying insert BLOB content into a table and using an oracle directory to get the content.
The procedure has been load blob content successfully if i am creating oracle directory which is pointing to local disk But when I try to create a directory which points to nfs volume, its not able to recognize it. I am getting following error when I run the procedure..


ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "DPA_CLN.LOAD_APPROVAL_IMAGE", line 59
ORA-06512: at line 2

Is it possible that oracle directory which points to nfs volume has some problem?


Thanks in Advance
Shail
Re: Problem with Oracle Directories [message #285775 is a reply to message #285774] Wed, 05 December 2007 10:13 Go to previous messageGo to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
One more thing I have checked privileges in directory and everything seems fine.
Re: Problem with Oracle Directories [message #285785 is a reply to message #285775] Wed, 05 December 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove what you said and copy and paste what you did with the result in a formated way as described in OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Problem with Oracle Directories [message #285797 is a reply to message #285774] Wed, 05 December 2007 12:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
shail_rh wrote on Wed, 05 December 2007 11:12


ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "DPA_CLN.LOAD_APPROVAL_IMAGE", line 59
ORA-06512: at line 2



File and directory must be on the server, not the client, but that's just a guess as an error message on its own does not really help.
Re: Problem with Oracle Directories [message #285821 is a reply to message #285774] Wed, 05 December 2007 15:11 Go to previous messageGo to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
Following are the steps which I took...


#Creating Table

CREATE TABLE ART_FILE

(

ID NUMBER(Cool,

BLOB_CONTENT BLOB,

);



#Creating Directory

CREATE OR REPLACE DIRECTORY LOAD_IMAGE_DETAIL AS '/mnt/images/woodstock/imagesDPA20';



GRANT READ, WRITE ON DIRECTORY LOAD_IMAGE_DETAIL TO DPA_CLN WITH GRANT OPTION;





# permission to that directory.

$ drwxrwxr-x 2 300 other 20234240 Dec 5 10:04 imagesDPA20





#Procedure to load images sitting in this directory to table

CREATE OR REPLACE PROCEDURE DPA_CLN.Load_Approval_Image_Detail IS



CURSOR C1 is SELECT approval_id,image_url

from DEV_APPROVAL_IMAGE where approval_id in (811101,811102,811103);



l_blob bLOB;

l_bfile BFILE:=BFILENAME('LOAD_IMAGE', '811098_conceptual_1.jpg' );

BEGIN

FOR C2 in C1 LOOP

l_bfile := BFILENAME('LOAD_IMAGE', c2.image_url );

INSERT INTO ART_FILE

( id,

blob_content

)

VALUES

(

c2.approval_id,

EMPTY_BLOB())

returning blob_content into l_Blob;

dbms_lob.fileopen(l_bfile);

dbms_lob.loadfromfile(l_Blob,l_bfile,dbms_lob.getlength(l_bfile));

dbms_lob.fileclose(l_bfile);

END LOOP;

END Load_Approval_Image_Detail;







ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "DPA_CLN.LOAD_APPROVAL_IMAGE", line 59
ORA-06512: at line 2

Re: Problem with Oracle Directories [message #285829 is a reply to message #285821] Wed, 05 December 2007 16:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Your directory names don't match. You have created load_image_detail, but in your procedure you are using load_image without the detail suffix.
Re: Problem with Oracle Directories [message #285893 is a reply to message #285821] Thu, 06 December 2007 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you follow the link I posted?

Regards
Michel
Re: Problem with Oracle Directories [message #286530 is a reply to message #285774] Fri, 07 December 2007 22:19 Go to previous messageGo to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
Michel,

Sorry I missed that link. Anyways I got my solution, I was not handling errors in exception. thats it.

thank you all for your response.

shail
Re: Problem with Oracle Directories [message #286538 is a reply to message #286530] Fri, 07 December 2007 23:44 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I got my solution, I was not handling errors in exception.

What do you mean?
You just added an "exception when others then null;" or the like block?

Regards
Michel

[Updated on: Fri, 07 December 2007 23:44]

Report message to a moderator

Previous Topic: Create Table via SELECT statement, can I ?
Next Topic: Check if a column is NULL
Goto Forum:
  


Current Time: Sat Dec 03 11:47:31 CST 2016

Total time taken to generate the page: 0.10311 seconds