ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception

[MAD_2]

Version information:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production

Error when running the attached code:
++++++++++++++++++++++++++++++++++++++++
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 103
ORA-06512: at "SYS.UTL_FILE", line 306
ORA-06512: at line 362
++++++++++++++++++++++++++++++++++++++++

Description of problem:
The code produces a report that is sent by email (only the PL/SQL code is attached, the script that takes care of sending the report produced and sent as attachment is ommited). However, at this point, the error shown above is recorded in the logs, and only 15 pages (1 per record) of the report are produced, the file produced reaches only a size of 22568 bytes (even though the data extracted from simply running the query in the code shows 100+ records).

The code is attached...

Any ideas of what may be happening here? By the way, I am not a DBA. Thanks!

[Graham Cameron_1]

There are a few things which may be going wrong.

Maybe the directory /data2/tmp doesn't exist.

Maybe the file /data2/tmp/divreg.doc already exists and you don't have permissions to overwrite it.

Is /data/tmp specified in the UTL_FILE_DIR statement in your init.ora file ?

-- Graham

[MAD_2]

Graham
Here is why am puzzled, because I did check every single one of those areas you touched on:

1. /data2/tmp does exist.
2. Although the file /data2/tmp/divreg.doc does exist, there are permissions to to overwrite it. As a matter of fact, as described above, the file is created but it only reaches a point where it is 22568 bytes large, that's where it stops.
3. /data/tmp is specified in UTL_FILE_DIR statement in our init.ora file.

Any other ideas?

As a matter of fact, everything was working up until Sat 3/13/04. This code has worked fine since Aug 2003.

[Geroge_1]

Hi,

I Have the very same problem. Mine is not generated by the same Object as yours but is exactly the same thing:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: Ï Îµ "ORDSYS.ORDSOURCE", line 354
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: Ï Îµ "ORDSYS.ORDVIDEO", line 580

Yes you are right it is very puzzling since I too have checked about the existence of my directories and files. The think to notice is that we encounter the same ORA exeption in Objects that are about File System. Maybe this notice might help further exp

[Indira Aramandla]

Hi,

Please check thse to see if this helps out.

1. declare a variable of type file_type

fHandle UTL_FILE.FILE_TYPE;

2. Directly specify the path ( I never used to create directory with public access)

fHandle := UTL_FILE.FOPEN('/u03/temp',filename,'W');

3. Assign some strings into the file

UTL_FILE.PUTF(fHandle,'%s','hai');

4. Close the file.

UTL_FILE.FCLOSE(fHandle);

5. Then you can check the file with the word 'hai' in the directory you specified.


I hope this helps

Indira A

[twang]

Hi,
Some info from oracle is as listed here:
Problem Description
-------------------

You are running a simple PL/SQL procedure which uses the UTL_FILE package.
When trying to write to a file you get the following errors:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at %sline %s

Example of a procedure calling UTL_FILE:

create procedure WRITEFILE is
fh UTL_FILE.FILE_TYPE;
path VARCHAR2(30);
name VARCHAR2(30);
begin
path := '/tmp';
name := 'test1';
fh := UTL_FILE.FOPEN(path, name, 'w');
UTL_FILE.PUT_LINE(fh, 'bla bla');
UTL_FILE.FCLOSE(fh);
end;

When you implement an exception handler to handle UTL_FILE exceptions, this
reveals you're running into the UTL_FILE.INVALID_OPERATION exception.

Solution Description
--------------------

There is problem in creating a file in the path you supplied or you are
trying to replace existing file, but do not have sufficient permission.

Firstly, check if you have your path included in the UTL_FILE_DIR parameter
in INIT.ORA:

SQL> show parameter utl_file
NAME TYPE VALUE
------------------------------------ ------- --------------------------
utl_file_dir string /tmp

Then look for operating system permissions on the directory and file you
are trying to create/replace:

$ cd /tmp
$ ls -ld . test1
drwxrwxrwt 15 root system 8192 May 25 10:35 .
-rw-r--r-- 1 root system 8 May 25 10:14 test1

Ensure that the user under which the Oracle instance is running has
sufficient rights to access this directory. Furthermore, if the file you
are trying to create already exists, verify if the owner and file
permissions are correct. YOu need to delete this file or reset file
ownership or file permissions. Then you can retry the PL/SQL procedure and
the file ownership and permissions:

SQL> exec WRITEFILE;
PL/SQL procedure successfully completed.
$ ls -l test1
-rw-r--r-- 1 oracle system 8 May 25 10:40 test1


Hope it helps.
twang

[theDALLA]

Hi

i have a related problem

i am editing the "init.ora" file and saving it (adding utl_file_dir = c:\test_dir ) and when i execute the command
SQL> show parameter utl_file ;

NAME TYPE VALUE
------------- ------- --------
utl_file_dir string

why the field value is always NULL??
please i want an answer

thak you,

theDALLA