asked on

Oracle Procedure - "File could not be opened or operated on as requested"

I 've created an Oracle procedure using the commands below:

CONNECT sys/passwor AS SYSDBA
CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'c:\oracle\extract';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO MAXTEST;
GRANT EXECUTE ON UTL_FILE TO MAXTEST;

CONNECT maxtest/maxtest

CREATE OR REPLACE PROCEDURE ITEM_csv AS
CURSOR item_cur IS
SELECT itemnum,
description
FROM item
ORDER BY itemnum;
header      varchar2(6);

v_file UTL_FILE.FILE_TYPE;
BEGIN
header := 'PART01';
v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR',
filename => 'item_csv.txt',
open_mode => 'w',
max_linesize => 32767);
FOR item_rec IN item_cur
LOOP
UTL_FILE.PUT_LINE(v_file,
       header                         || ',' ||
       item_rec.itemnum            || ',' ||
item_rec.description);
END LOOP;

UTL_FILE.FCLOSE(v_file);

EXCEPTION

WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.');

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.');

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.');

WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.');

WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.');

WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.');

WHEN UTL_FILE.CHARSETMISMATCH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' ||
'operations use nonchar functions such as PUTF or GET_LINE.');

WHEN UTL_FILE.FILE_OPEN THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.');

WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
'be within the range 1 to 32767.');

WHEN UTL_FILE.INVALID_FILENAME THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.');

WHEN UTL_FILE.ACCESS_DENIED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.');

WHEN UTL_FILE.INVALID_OFFSET THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
'it should be greater than 0 and less than the total ' ||
'number of bytes in the file.');

WHEN UTL_FILE.DELETE_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.');

WHEN UTL_FILE.RENAME_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.');

WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;

END;

When I execute the procedure I keep getting "-20003, 'File could not be opened or operated on as requested".

Please help.

dovidf

There are a number of operations that could fail.

Remove this clause

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.');

to get the line number in the procedure where the error occurs or output the line number to some location before the raise error.

Naveen Kumar

put some debug statements like and only that will help.

dbms_output.put_line('i am here 1');
..
fopen(..);
dbms_output.put_line('i am here 2');
.. put_line(..);
dbms_output.put_line('i am here 3');
fclose(..);
dbms_output.put_line('i am here 4');

With a bit of debugging you can identify yourself. Thanks,

Set serveroutput on --> should be given in sqlplus before you execute the procedure so that our debug stmts can come on the screen.

If the last debug stmt u get to see is 2 then obviously there is some issue with put_line(..) and we can concentrate on what is the issue there. similarly we can do this till we get rid of all errors.

Thanks

Naveen Kumar

Though i think this is not an issue but from the url

http://www.psoug.org/reference/utl_file.html

CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'c:\oracle\extract\'; ---> added a \ at the end and before the '

Thanks

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kleinrace

ASKER

Looks like it was permission problem.Thanks sujith80

Oracle Procedure - &quot;File could not be opened or operated on as requested&quot;

Oracle Procedure - "File could not be opened or operated on as requested"