ORA-06502 [message #426250] |
Wed, 14 October 2009 10:15 |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
I am getting
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3
I want to pass the values returned from a cursor to a procedure
is below the correct approach
Begin
for rec in (select employee_id,dept_id,last_nme,salary from employees )loop
--calling procedure
sp_ins_emp_hist(rec.employee_id,null,null,rec.dept_id,rec.last_nme,rec.salary);
commit;
end loop;
exception
when others then raise;
end;
|
|
|
Re: ORA-06502 [message #426251 is a reply to message #426250] |
Wed, 14 October 2009 10:19 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
In general it should work (if the data types of the record variables match the parameter data types of the procedure, both are unknown to us.)
Get rid of the
exception
when others then raise;
though, it just hides the real error and does nothing to handle it.
|
|
|
Re: ORA-06502 [message #426253 is a reply to message #426251] |
Wed, 14 October 2009 10:28 |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Sir,Thanks for your reply
But I got the same error when I tried removing when others exception.
Also the data types are same .
when I pass values manually returned from the cursor the
procedure sp_ins_emp_hist is successfully executed.
But when i ran as a batch, its giving error.
|
|
|
|
Re: ORA-06502 [message #426256 is a reply to message #426253] |
Wed, 14 October 2009 10:31 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Then the data types are not the same, or you run into an error inside the procedure.
Post the actual SQLPlus session where you execute the code, including all error messages.
And post the result of:
and
|
|
|
Re: ORA-06502 [message #426257 is a reply to message #426250] |
Wed, 14 October 2009 10:32 |
cookiemonster
Messages: 13951 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well you're overlooking something, but since you've given use nothing to work with we can't tell you what it is.
Post:
1) A desc of the employees table.
2) Specification of sp_ins_emp_hist procedure.
Did the line number in the error message change after you removed the exception handler?
|
|
|
Re: ORA-06502 [message #426283 is a reply to message #426253] |
Wed, 14 October 2009 12:55 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
prachij593 wrote on Wed, 14 October 2009 11:28
But I got the same error when I tried removing when others exception.
That's because the WHEN OTHERS had nothing to do with the error. Thomas was just telling you that in addition to your error, that is just plain bad code.
|
|
|
|
Re: ORA-06502 [message #426343 is a reply to message #426319] |
Thu, 15 October 2009 00:46 |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
The plsql numeric or value error can also be caused by associative array / plsql table, if it is empty.
XE@SQL> declare
2 type p_Table is table of number index by pls_integer;
3 l_Table p_table;
4 begin
5 for i in l_table.first..l_table.last
6 loop
7 null;
8 end loop;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
XE@SQL>
|
|
|
Re: ORA-06502 [message #426442 is a reply to message #426250] |
Thu, 15 October 2009 08:41 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Yes, forgot about that one. It is obscure.
But the actual problem is not that the array is empty, but rather that one of your loop indexes is null
SQL> begin
2 for i in 1..null loop
3 null;
4 end loop;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 2
SQL> begin
2 for i in null..1 loop
3 null;
4 end loop;
5 end;
6 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 2
Kevin
|
|
|