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.

kleinraceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dovidfCEOCommented:
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 ManagerCommented:
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 ManagerCommented:
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
SujithData ArchitectCommented:
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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kleinraceAuthor Commented:
Looks like it was permission problem.Thanks sujith80
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.