Home » SQL & PL/SQL » SQL & PL/SQL » types of pl/sql value or numric error. (Oracle 10g)
types of pl/sql value or numric error. [message #572207] Fri, 07 December 2012 07:05 Go to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Hi All,

How many types of pl/sql value or numeric errors are there and when occurs.Like as follows

1.ORA-06502: PL/SQL: numeric or value error: character string buffer too small
2.ORA-06502: PL/SQL: numeric or value error: number precision too large

Regards,
Nathan
Re: types of pl/sql value or numric error. [message #572208 is a reply to message #572207] Fri, 07 December 2012 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix the errors.

Regards
Michel
Re: types of pl/sql value or numric error. [message #572211 is a reply to message #572208] Fri, 07 December 2012 07:32 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Hi All,

Actually I was discussing about the following code. It is throwing ORA-06502: PL/SQL: numeric or value error, but not showing the reason behind.Please help me to resolve this issue.

create table dept1 as select * from dept where 1=2;

declare
vv varchar2(200):='select deptno,dname,loc from dept1';
type depp_tab is table of dept1%rowtype;
t_depp depp_tab;
begin
execute immediate vv bulk collect into t_depp;
for i in t_depp.first .. t_depp.last loop
insert into scott.dept values(t_depp(i).deptno,t_depp(i).dname,t_depp(i).loc);
end loop;
end;


Regards,
Nathan
Re: types of pl/sql value or numric error. [message #572212 is a reply to message #572211] Fri, 07 December 2012 07:47 Go to previous messageGo to next message
ThomasG
Messages: 3067
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
sss111ind wrote on Fri, 07 December 2012 14:32
It is throwing ORA-06502: PL/SQL: numeric or value error, but not showing the reason behind.


Show us the SQL*Plus session where this happens.
Re: types of pl/sql value or numric error. [message #572213 is a reply to message #572211] Fri, 07 December 2012 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 10593
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many columns does dept have?
Re: types of pl/sql value or numric error. [message #572215 is a reply to message #572213] Fri, 07 December 2012 08:04 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Actually I have run in Toad so exactly the following error is showing when I run the above code.

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


And dept is having 3 columns named as deptno,dname,loc.

Regards,
Nathan
Re: types of pl/sql value or numric error. [message #572216 is a reply to message #572215] Fri, 07 December 2012 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 57652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Through away TOAD that does not help you in anyway and use SQL*PLus:
SQL> declare
  2  vv varchar2(200):='select deptno,dname,loc from dept1';
  3  type depp_tab is table of dept1%rowtype;
  4  t_depp depp_tab;
  5  begin
  6  execute immediate vv bulk collect into t_depp;
  7  for i in t_depp.first .. t_depp.last loop
  8  insert into scott.dept values(t_depp(i).deptno,t_depp(i).dname,t_depp(i).loc);
  9  end loop;
 10  end;
 11  
 12  
 13  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7

There is nothing in your table so FIRST and LAST are undefined.

Learn how to indent the code, it will also help you to write, understant and maintain your code.
If you don't know, use SQL Formatter.

Regards
Michel
Re: types of pl/sql value or numric error. [message #572218 is a reply to message #572216] Fri, 07 December 2012 08:18 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member



So I added the following if clause in the code,for not throwing the exception.Is it okay.
declare
vv varchar2(200):='select deptno,dname,loc from dept1';
type depp_tab is table of dept1%rowtype;
t_depp depp_tab;
begin
execute immediate vv bulk collect into t_depp;
if t_depp.count>0 then 
for i in t_depp.first .. t_depp.last loop
insert into scott.dept values(t_depp(i).deptno,t_depp(i).dname,t_depp(i).loc);
end loop;
end if;
end;


Regards,
Nathan
Re: types of pl/sql value or numric error. [message #572219 is a reply to message #572218] Fri, 07 December 2012 08:37 Go to previous message
Michel Cadot
Messages: 57652
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback but think about code format.

Regards
Michel
Previous Topic: Difference in num_rows and COUNT function.
Next Topic: update query
Goto Forum:
  


Current Time: Fri Apr 25 01:30:09 CDT 2014

Total time taken to generate the page: 0.13335 seconds