How to use a stored procedure to read a file into an oracle DB

3
votes

I'm trying to read a file into my oracle table as a blob. The file is *.gz data. I looked around the 'net and found some examples, and this is what I've come up with:

create or replace PROCEDURE upload_supp_data
IS
   src_file   BFILE;
   dst_file   BLOB;
   lgh_file   BINARY_INTEGER;
   data_dir varchar2(20) := '/tmp/';
   file_name varchar2(50) := '200912020200.rep-ids-top50-sip.txt.gz';
BEGIN

   src_file := BFILENAME (data_dir, file_name);

   -- insert a NULL record to lock
   INSERT INTO alarms_supplemental
               (alarm_id, resource_id, supplementaldata
               )
        VALUES (13794740, 1, EMPTY_BLOB ()
               )
     RETURNING supplementaldata
          INTO dst_file;

   -- lock record
   SELECT  supplementaldata
         INTO dst_file
         FROM alarms_supplemental
        WHERE alarm_id = 13794740
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   -- determine length
   lgh_file := DBMS_LOB.getlength (src_file);
   -- read the file
   DBMS_LOB.loadfromfile (dst_file, src_file, lgh_file);

   -- update the blob field
     UPDATE ALARMS_SUPPLEMENTAL
      SET supplementaldata = dst_file
    WHERE ALARM_ID = 13794740;

   -- close file
   DBMS_LOB.fileclose (src_file);
END upload_supp_data;

When I run this, I get these errors:

ORA-22285: non-existent directory or file for FILEOPEN operation

ORA-06512: at "SYS.DBMS_LOB", line 635

ORA-06512: at "AIP_DBA.UPLOAD_SUPP_DATA", line 29

ORA-06512: at line 2

Process exited.

I've played around with the path various ways, ie '/tmp/', 'tmp', '/tmp'. The filename is correct, so I'm at a loss as to what's wrong. This is actually the first stored procedure I've ever written, so this might be a really simple thing. I hope someone can help me with this. I'm using Oracle SQL Developer, by the way.

3
oraclestored-proceduresblobora-06512

3 Answers

5
votes

You would need the DBA to do:

CREATE DIRECTORY brian_tmp AS '/tmp';
GRANT READ, WRITE ON DIRECTORY brian_tmp TO brian;

Then in place of /tmp/ in your code, you would put brian_tmp. The DBA might not want to give you access to all of /tmp (as your user can now do anything in that directory masquerading as the Unix user Oracle is running as) in which case you would need a subdirectory.

4
votes

You need the DBA to create the Directory object in Oracle (not the directory on disk). Something like:

CREATE DIRECTORY admin AS 'oracle/admin';

Then permissions are granted to the directory; like other schema ojbects are (Views, packages etc...)

2
votes

In addition to the other answers, note that when you use your directory, e.g.:

CREATE DIRECTORY my_dir as '/tmp';
GRANT READ, WRITE ON DIRECTORY my_dir TO this_user;
...
data_dir varchar2(20) := 'MY_DIR';

The directory name must be in upper case unless specifically created in lower case via the use of double quotes in the CREATE statement:

CREATE DIRECTORY "My_Dir" AS '/tmp';

In which case you'd always have to refer to the name in double quotes in SQL, and in the proper case in programmatic refereces:

GRANT READ, WRITE ON DIRECTORY "My_Dir" TO this_user;
...
data_dir varchar2(20) := 'My_Dir';