2

I want to read a file on my local machine that contains query parameters when I execute a query in Oracle SQL developer. The examples that I've found on the web so far are inadequate. I keep getting "ORA-29283: invalid file operation" errors when I execute the below code:

CREATE DIRECTORY SAMPLEDATA2 AS 'C:';
GRANT READ, WRITE ON DIRECTORY SAMPLEDATA2 TO PUBLIC;


declare
f utl_file.file_type;
s varchar2(200);
c number := 0;

BEGIN

f := utl_file.fopen('SAMPLEDATA2','sample2.txt','R');
loop
    utl_file.get_line(f,s);
    dbms_output.put_line(s);
    c := c + 1;
end loop;

exception
    when NO_DATA_FOUND then
        utl_file.fclose(f);
        dbms_output.put_line('Number of lines: ' || c);
end;
APC
  • 144,005
  • 19
  • 170
  • 281
user786149
  • 21
  • 1
  • 1
  • 2
  • possible duplicate of [UTL_FILE.FOPEN() procedure not accepting path for directory ?](http://stackoverflow.com/questions/2751113/utl-file-fopen-procedure-not-accepting-path-for-directory) – APC Jun 06 '11 at 18:27

2 Answers2

4

UTL_FILE can only read data from files that are stored on the database server. Since it is PL/SQL code, it runs on the database server and only has access to the resources that are available to the Oracle process on the database server.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • O.K., that's why that's failing, thanks. Is there an alternative that I can use to read a local file and send it to the database? – user786149 Jun 06 '11 at 16:54
  • 1
    @user786149 - Not really. You could do something like use SQL*Loader to write the file to a CLOB column in a table in the database and then use the `DBMS_LOB` package to read the data from the CLOB. But that would be quite different semantics. You would generally be better off copying the file to the database server and using `UTL_FILE` to read the file (assuming, of course, that you wouldn't be better off using SQL*Loader to just load the data from the file into a table). – Justin Cave Jun 06 '11 at 17:14
0

"I want to read a file on my local machine that contains query parameters when I execute a query in Oracle SQL developer."

This seems an unusual - I was going to say 'peculiar' -architectural decision. Where do these values comne from? Why do thay have to be stored in a file? How often do they change?

It is going to be very difficult to expose the contents of a local PC file to a remote database server (i.e. we're talking automating it with ftp or a manual process involving something like WinSCP).

On the other hand, it could be quite simple to apply some query parameters to a query; for instance by using SYS_CONTEXT and namespaces. But I need to know more details before I can provide an alternative solution.

APC
  • 144,005
  • 19
  • 170
  • 281
  • It's a one time data migration procedure that involve a dozen different tables and around 1500 distinct dates that are not consecutive. I have all the information in a flat file so I thought it would be simplest to read that flat file as input to the migration scripts. – user786149 Jun 06 '11 at 19:51
  • Well, heck, if it's a one-time load, screw elegance and turn your data into a script of a bunch of insert statements and toss it all into a temp table for further massaging later. (I do this in Excel personally.) – Mark Bowytz Jun 11 '11 at 19:40