Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00604/ORA-06502 on create package, validates after recompile (Oracle 9.2.0.7.0, AIX 5.2)
ORA-00604/ORA-06502 on create package, validates after recompile [message #421442] Tue, 08 September 2009 05:50 Go to next message
IvanGeentjens
Messages: 1
Registered: September 2009
Junior Member
We have a problem on our production environment.

When installing a new release, all the packages that were created become INVALID, with the following error :

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11

However, when recompiling the package, it becomes VALID .

The execution of the code in the packages became up to 10 times slower after this install.

We run Oracle 9.2.0.7.0 on an AIX 5.2

Any ideas ?
Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #421446 is a reply to message #421442] Tue, 08 September 2009 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, there's almost certainly some confusion here.

1) How are you compiling the new packages?

2) After compilation, when you look in USER_ERRORS, is there an error for every single new package, or just for one package?

3) Do just the new packages run slowly, or do all packages run slowly?
Is it just the first time that you call a package that it is slow, or is every call to every object in the package slower?

4) Are there any trace files generated?

5) Do you have any home grown auditing code that might be firing when you change code?

The problem with the error you're getting is that it is a runtime error, not a compilation error - implying that either the error isn't happening when you say it is, or that you've got some code you're not telling us about that runs as part of your install or on compilation of objects on the DB.
Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #428968 is a reply to message #421442] Sat, 31 October 2009 04:02 Go to previous messageGo to next message
jsphatt
Messages: 1
Registered: October 2009
Junior Member
I'm getting a similar error right now. We can compile existing packages without a problem. But now, on a few, when we try to rerun the create package scripts we get the following error:

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

On the 3 packages that are failing, we get the same error...always at line 8.

If we remove a procedure from a failing package, it will run OK, but the procedure has to be deleted. Commenting it out will not get rid of the error.

This is the first time we've seen this problem and it's after an upgrade from 10.1 to 10.2.0.4 and changing compilitation from interpreted to native.

It only makes sense to me that some underlying process is used to execute the create package command and it can't handle the number of characters in the create package command.

Thoughts?

Oh, and when the create package fails, the package is not changed in the database. The existing version persists and is valid.
Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #428973 is a reply to message #428968] Sat, 31 October 2009 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thoughts?

Without being able to reproduce the same thing, none.

Regards
Michel

[Updated on: Sat, 31 October 2009 05:06]

Report message to a moderator

Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #429047 is a reply to message #421442] Sun, 01 November 2009 13:46 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Reason: you have a variable somewhere in your code,And that variable is not wide enough to hold the data you put into it


Can you show us your code?

Just to add one point here. May be it would be helpful for you.
Block the below code if its there in your package to debug.you
should see the actual error..
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

blocks - you'll discover immediately WHERE this is happening and then you can use your programming skills to correct it.



SQL> declare
  2   v_str varchar2(2);
  3   begin
  4   select col1 into v_str from test021109 where rownum=1;
  5   dbms_output.put_line(v_str);
  6   end;
  7    /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4



SQL> select col1 from test021109;

COL1
----
CL
OP

SQL> select LENGTH(col1) from test021109;

LENGTH(COL1)
------------
4
4

SQL> declare
  2   v_str varchar2(2);
  3   begin
  4   select trim(col1) into v_str 
  5      from test021109 
  7       where  rownum=1;
  8   dbms_output.put_line(v_str);
  9   end;
  10   /
CL
PL/SQL procedure successfully completed.



Regards,
Ved

[Updated on: Sun, 01 November 2009 14:04]

Report message to a moderator

Re: ORA-00604/ORA-06502 on create package, validates after recompile [message #429078 is a reply to message #429047] Mon, 02 November 2009 01:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since the error happens at compilation time, it is most likely not something in the package itself.
My bet would be that there is either some system-trigger, or the compilation is done with debug option.
Previous Topic: Rank in query
Next Topic: An interesting problem: same query doesn't running on different database.
Goto Forum:
  


Current Time: Thu Sep 29 22:48:36 CDT 2016

Total time taken to generate the page: 0.21694 seconds