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

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.

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...)

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';