Solved

external directory error in oracle

Posted on 2013-01-28
12
482 Views
Last Modified: 2013-01-28
I have created a procedure

CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image IS
tmpVar NUMBER;
dest_loc  BLOB;
src_loc   BFILE := BFILENAME('CONTRACTS', '2011 HeartlandEmployeeReferralCard.pdf');

BEGIN
   
   
    -- +-------------------------------------------------------------+
    -- | INSERT INITIAL BLOB VALUE (an image file) INTO THE TABLE    |
    -- +-------------------------------------------------------------+
    INSERT INTO test_blob (id, file_name, image, timestamp)
        VALUES (1001, '2011 HeartlandEmployeeReferralCard.pdf', empty_blob(), sysdate)
        RETURNING image INTO dest_loc;

    -- +-------------------------------------------------------------+
    -- | OPENING THE SOURCE BFILE IS MANDATORY                       |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

    -- +-------------------------------------------------------------+
    -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN  |
    -- +-------------------------------------------------------------+
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));

    -- +-------------------------------------------------------------+
    -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);

    COMMIT;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END Load_BLOB_From_File_Image;

When I run this procedure I get an error.


ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "HBC_DATA.LOAD_BLOB_FROM_FILE_IMAGE", line 65
ORA-06512: at line 3
ext-dir.JPG
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38827463
Post the results of the following two commands:
ls -al /home/hbc_data/ldon1
and
ls -al /home/hbc_data/ldon1/contracts
0
 
LVL 6

Author Comment

by:anumoses
ID: 38827494
$ ls -al /home/hbc_data/ldon1
/home/hbc_data/ldon1 not found
$ ls -al /home/hbc_data/ldon1/contracts
/home/hbc_data/ldon1/contracts not found
pwd

/ldon1/contracts

when I log to heart1
 and do pwd its /home/hbc_data
then I do cd one space /ldon1
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38827523
Did I typo the folder wrong from the image you posted?

The folder must exist on the database server and the oracle unix uer needs read access to the folder.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:anumoses
ID: 38827565
$ cd home
$ pwd
/home
$ cd hbc_data
$ pwd
/home/hbc_data
$ cd /ldon1
$ cd contracts
$
0
 
LVL 6

Author Comment

by:anumoses
ID: 38827567
$ cd home/hbc_data /ldon1/contracts
sh: cd: The specified substitution is not valid for this command.
$
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38827578
>>cd /ldon1

This is cd'ing into a root folder.

>>sh: cd: The specified substitution is not valid for this command.

This has a space in it.  Does the folder contain a space?

Post the results of:
ls -al /home/hbc_data
0
 
LVL 6

Author Comment

by:anumoses
ID: 38827596
I am attaching
dir.txt
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38827623
I don't see any folder resembling ldon1.  That is the reason for your "ORA-22288: file or LOB operation FILEOPEN failed No such file or directory" error.

The image you posted for the creation of the CONTACTS directory, looks like you are trying to reference an ldon1 folder and it doesn't appear to exist where you specified it in the create directory syntax.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38827640
$ pwd
/home/hbc_data
$ cd /ldon1/contracts
$ pwd
/ldon1/contracts
$ ls
2011 HeartlandEmployeeReferralCard.pdf
$
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38827659
>>/ldon1/contracts

Then this needs to be the oracle directory.  Look at the original image you uploaded.  It has /home/hbc_data in front of /ldon1/contracts:

create or replace directory contracts as '/ldon1/contracts';
0
 
LVL 6

Author Comment

by:anumoses
ID: 38827698
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38827745
thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question