Reputation: 1347
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.
Upvotes: 3
Views: 10587
Reputation: 43533
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';
Upvotes: 2
Reputation: 2595
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.
Upvotes: 5
Reputation: 13571
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...)
Upvotes: 4