Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

MAD_2
Super Advisor

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

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!
Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with
6 REPLIES
Graham Cameron_1
Honored Contributor

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

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
MAD_2
Super Advisor

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

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.
Contrary to popular belief, Unix is user friendly. It's just very particular about who it makes friends with
Geroge_1
Occasional Visitor

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

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
Honored Contributor

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

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
Never give up, Keep Trying
twang
Honored Contributor

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

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
Occasional Visitor

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

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