Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error
ORA-06502: PL/SQL: numeric or value error [message #192036] Sun, 10 September 2006 02:22 Go to next message
rdprasad
Messages: 2
Registered: September 2006
Junior Member
Hi Everybody,

I am facing an issue with the following function which i created:

CREATE OR REPLACE
function getmaxlenvarchar(p_dummy in number) return varchar2 is
vret varchar2(32700);
err varchar2(4000);
begin
for i in (select * from all_objects where object_type in ('TABLE','VIEW') and rownum<1800)loop
vret:=vret||i.owner||'.'||i.object_name||',';
end loop;
dbms_output.put_line(to_char(length(vret)));
return vret;
exception when others then
err:=sqlerrm;
DBMS_OUTPUT.PUT_LINE(err);
return 'No Chance';
end getmaxlenvarchar;


This function basically returns a very long varchar2 of length of almost 32000.

When i try to call this function from a small PL/SQL block it is yielding :

declare
vret varchar2(32700);
begin
vret:=getmaxlenvarchar(123);
dbms_output.put_line(length(vret));
end;

***********
OUTPUT
***********

ORA-06502: PL/SQL: numeric or value error
9

can anyone please explain why this is happening.

I am using Oracle 9i.

Any help ASAP is highly appreciated.

Thanks,
RDP
Re: ORA-06502: PL/SQL: numeric or value error [message #192037 is a reply to message #192036] Sun, 10 September 2006 03:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This is what Actually happened ..

The Functioned raised an exception.
On exception handling section it returned Error message

Quote:

ORA-06502: PL/SQL: numeric or value error


return 'No Chance';

is returned from the function. and its length is displayed as 9 in the OUTPUT.

Thumbs Up
Rajuvan

Re: ORA-06502: PL/SQL: numeric or value error [message #192043 is a reply to message #192037] Sun, 10 September 2006 08:01 Go to previous messageGo to next message
rdprasad
Messages: 2
Registered: September 2006
Junior Member
Thanks for the prompt reply.

That is understood. But why am i getting this exception is the
actual issue.

I am storing the data less than 32000 in the varchar2 variable but still i am getting this exception.

So was confused.

Thanks...
Re: ORA-06502: PL/SQL: numeric or value error [message #192050 is a reply to message #192043] Sun, 10 September 2006 10:38 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather say that your assumption is wrong; you only THINK that string length is less than 32000 characters, but, actually, it is larger.

VRET variable stores owner name and object name, dot in between and comma separates such pairs. My 10g database says this:
SQL> SELECT AVG(LENGTH(owner)), AVG(LENGTH(object_name))
  2  FROM all_objects
  3  WHERE object_type IN ('TABLE', 'VIEW');

AVG(LENGTH(OWNER)) AVG(LENGTH(OBJECT_NAME))
------------------ ------------------------
        3.89307049               18.1409797

SQL>
If we round those values, let's assume that an average length of the VRET = 4 + 1 (dot) + 18 + 1 (comma) = 24.

If you take 1800 pairs, the whole length is 1800 x 24 = 43200 characters.

Change the cursor declaration; restrict number of returned records to a smaller value; for example, "... AND ROWNUM < 100" and you won't get that error message again.

[EDIT]

I forgot to mention that procedure, actually, has nothing to do with the testing code -> getmaxlenvarchar(123). Why do you use "123" when "p_dummy" is never used in the procedure?

[Updated on: Sun, 10 September 2006 10:41]

Report message to a moderator

Previous Topic: Regarding Stored procedure
Next Topic: Need help in updating blob column
Goto Forum:
  


Current Time: Fri Dec 02 12:44:10 CST 2016

Total time taken to generate the page: 0.08689 seconds