Re: pl*sql errors...

ksjune wrote
>ORA-06510: PL/SQL: unhandled user-defined exception
>ORA-06512: at "SYS.UTL_FILE", line 82
>ORA-06512: at "SYS.UTL_FILE", line 120
>ORA-06512: at "TESTDBA.MAKE_SQL", line 91
>ORA-06512: at line 1

Most likely, you do not have access rights to the file system. Your Oracle DBA should adjust init.ora to allow any file access. For each directory where you want to read or write, add a line like:

    utl_file_dir = /tmp
    utl_file_dir = /tmp/spool
    utl_file_dir = /home/ksjune/spool

Note that you can only access the machine where Oracle is running. You cannot write to your own harddisk. System user Oracle needs access to the directories given above, not the current SQL*Plus user.

Furthermore, you should add some exception handling to your code, to find out what went wrong! I bet you'll get Invalid Path, which is raised if the directory is not given in init.ora. Invalid Operation is raised if the path is given in init.ora, but user oracle does not have access rights to the directory or file.

exception

    when utl_file.invalid_filehandle then       raise_application_error( -20001, 'utl_file: Invalid filehandle', true);

    when utl_file.invalid_mode then
      raise_application_error( -20002, 'utl_file: Invalid mode', true);     when utl_file.invalid_operation then

• will also be raised if file system permissions are not OK (note
• that user oracle needs access, not the actual user of utl_file),
• or on WinNT server when trying to Append to a new file: raise_application_error( -20003, 'utl_file: Invalid operation', true); when utl_file.invalid_path then raise_application_error( -20004, 'utl_file: Invalid path ', true); when utl_file.read_error then raise_application_error( -20005, 'utl_file: Read error', true); when utl_file.write_error then raise_application_error( -20006, 'utl_file: Write error', true); end;

Arjan. Received on Fri Mar 12 1999 - 10:23:13 CST