• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

external directory error in oracle

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
anumoses
Asked:
anumoses
  • 7
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
Post the results of the following two commands:
ls -al /home/hbc_data/ldon1
and
ls -al /home/hbc_data/ldon1/contracts
0
 
anumosesAuthor Commented:
$ 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
 
slightwv (䄆 Netminder) Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
anumosesAuthor Commented:
$ cd home
$ pwd
/home
$ cd hbc_data
$ pwd
/home/hbc_data
$ cd /ldon1
$ cd contracts
$
0
 
anumosesAuthor Commented:
$ cd home/hbc_data /ldon1/contracts
sh: cd: The specified substitution is not valid for this command.
$
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
anumosesAuthor Commented:
I am attaching
dir.txt
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
$ pwd
/home/hbc_data
$ cd /ldon1/contracts
$ pwd
/ldon1/contracts
$ ls
2011 HeartlandEmployeeReferralCard.pdf
$
0
 
slightwv (䄆 Netminder) Commented:
>>/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
 
anumosesAuthor Commented:
0
 
anumosesAuthor Commented:
thanks
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now