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

kleinrace
kleinrace used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
dovidfCEO

Commented:
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 KumarProduction Manager / Application Support Manager

Commented:
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 KumarProduction Manager / Application Support Manager

Commented:
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
Data Architect
Commented:
Looks like there is nothing wrong with the code.

It has to be a permission issue with the directory. Do you have the directory c:\oracle\extract existing in your machine? If not, create the same from the OS.

Try to create the directory as user MAXTEST
OR
GRANT ALL ON DIRECTORY EXTRACT_DIR TO MAXTEST;

Author

Commented:
Looks like it was permission problem.Thanks sujith80

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial