Getting utl_file.invalid_path exception in Oracle9i

[hopgal]

I have written a script to read data from a file, execute a SQL query, then write the data to an output file. I was getting this error: "ORA-20000: utl_file.invalid_path" when I had my read/write files in C:\scripts on my local drive. In SQL*Plus, I executed "show parameters" and the utl_file_dir is set to "/oracle/app/oracle/admin/{dbname}/udump". I tried created this path on my C: drive and copied the files in that I wanted to read and write to/from to this new directory. I still get the same error. I tried executing ALTER SYSTEM SET UTL_FILE_DIR='C:\scripts' and got the error "ORA-02095: specified initialization parameter cannot be modified"

I am not the DBA, and if I ask the DBA to change this parameter for me, I know he won't do it. He is totally uncooperative, and won't make any changes we need to the database. Is there some way I can change this parameter for myself only, locally, without any help from the DBA?

Here is my script also, in case there is something else wrong with it that may be causing this error (I have thoroughly tested this SELECT statement and it is working fine, I just don't want to have to execute it 100 times and cut and paste the results into Excel!):

DECLARE
NEXT_CASE_ID_NUMBER varchar2(20);
input_file utl_file.file_type;
output_file utl_file.file_type;
BEGIN
input_file := utl_file.fopen('\scripts', 'case_ids.txt', 'r');
output_file := utl_file.fopen('\scripts', 'output_1.txt', 'w');
LOOP
utl_file.get_line(input_file, NEXT_CASE_ID_NUMBER);
utl_file.put(output_file, NEXT_CASE_ID_NUMBER);
utl_file.put(output_file,
'SELECT p.person_id, p.first_name, p.last_name || " INCOME",
i.INCOME_TYPE_CD AS ITEM_TYPE_CD, i.PERIOD_TYPE_CD, i.START_DT,
i.END_DT, i.GROSS_AMOUNT AS AMOUNT
FROM person p, income i
WHERE p.person_id (+) = i.person_id
AND p.person_id in
(select PERSON.PERSON_ID
FROM PERSON, CASE_MEMBER
WHERE PERSON.PERSON_ID = CASE_MEMBER.PERSON_ID
AND case_id = NEXT_CASE_ID_NUMBER)
UNION
select p.person_id, p.first_name, p.last_name || " ASSET",
a.ASSET_TYPE_CD AS ITEM_TYPE_CD, "X", a.START_DT, a.END_DT, a.CASH_VALUE AS AMOUNT
FROM person p, asset a
WHERE p.person_id (+) = a.person_id
AND p.person_id in
(select PERSON.PERSON_ID
FROM PERSON, CASE_MEMBER
WHERE PERSON.PERSON_ID = CASE_MEMBER.PERSON_ID
AND case_id= NEXT_CASE_ID_NUMBER)
UNION
select p.person_id, p.first_name, p.last_name || " EXPENSE",
e.EXPENSE_TYPE_CD AS ITEM_TYPE_CD, e.PERIOD_TYPE_CD,e.START_DT,
e.END_DT, e.AMOUNT
FROM person p, expense e
WHERE p.person_id (+) = e.person_id
AND p.person_id in
(select PERSON.PERSON_ID
FROM PERSON, CASE_MEMBER
WHERE PERSON.PERSON_ID = CASE_MEMBER.PERSON_ID
AND case_id = NEXT_CASE_ID_NUMBER)');
utl_file.new_line(output_file);
END LOOP;

IF utl_file.is_open(input_file) THEN
utl_file.fclose(input_file);
END IF;
IF utl_file.is_open(output_file) THEN
utl_file.fclose(output_file);
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.invalid_path');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.invalid_mode');
WHEN utl_file.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.READ_ERROR');
WHEN utl_file.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.WRITE_ERROR');
WHEN utl_file.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.INTERNAL_ERROR');
WHEN utl_file.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.INVALID_FILEHANDLE');
WHEN utl_file.INVALID_MAXLINESIZE THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.INVALID_MAXLINESIZE');
WHEN utl_file.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.INVALID_OPERATION');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR');


END;

 

[SantaMufasa]

Hopgal,

You have hit an Oracle problem that REALLY bugs me, as well. The problem is that Oracle has built the "utl_file." package to write to the server only (in your case a Unix- or Linux-based system)...You are trying cause "utl_file..." to write to your client PC. That is why you are receiving the errors your are receiving.

The DBMS_OUTPUT.PUT_LINE packaged procedure writes to your client PC, but has a 1MB limit to the amount of output you can create per session (using the "DBMS_OUTPUT.ENABLE(1000000)" command).

So, you can use "utl_file..." to output to your server, then ftp the resulting file back to your PC for incorporation into your spreadsheet, or you can output using "dbms_output.put_line" in conjunction with the SPOOL <filename> command to write a file to your PC client.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:30 (23Mar04) UTC (aka "GMT" and "Zulu"), 18:30 (22Mar04) Mountain Time)

 

[sem]

Can you explain your original task? Isn't case_ids.txt a result of another query? If so you may manage without utl_file at all. Just spool to the file not only id's, but the whole command and then run that "command" script. You may also consider utilizing lexical variable to call the same script with multiple parameters. In this case you don't need to spool the whole statement, but rather a

@<script name> id

where script name is the name of the file where your query is stored.

Regards, Dima

http://seminihin.narod.ru

 

[hopgal]

The case_ids file contains a list of test case id's. It is not the result of a query. I am trying to extract some data from a test database and put it into text files so that it is in a format I can use for input into a LoadRunner script. That's why I need the output to go into a file.

I hate the way Oracle assumes that everybody will have access to the server, as if everyone who uses Oracle is a DBA!

 

[sem]

Oracle doesn't assume this, but how can server side package access you client pc???
Why don't you write a simple script looping through your lines and calling sql*plus with an appropriate parameter? Nobody forces you to use UTL_FILE!

Regards, Dima

http://seminihin.narod.ru

 

[hopgal]

Dima, I didn't know this was a server-side package. I thought it was a feature built into PL/SQL, like a library function in C. It would seem like a good thing to have.

Thanks to everybody for your help & suggestions, but I decided to just write a Java class to do what I need. Oh well, at least I learned some new stuff about PL/SQL.

 

[sem]

But pl/sql is always executed on server! The only exception I know is Oracle Developer tool set: the products of this line has their own client-side pl/sql engine.

Regards, Dima

http://seminihin.narod.ru