Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502 (ORACLE 10.2.0.3)
ORA-06502 [message #426250] Wed, 14 October 2009 10:15 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #426255 is a reply to message #426253] Wed, 14 October 2009 10:31 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Post the code of sp_ins_emp_hist procedure.

Re: ORA-06502 [message #426256 is a reply to message #426253] Wed, 14 October 2009 10:31 Go to previous messageGo to next message
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:

desc sp_ins_emp_hist


and

desc employees 

Re: ORA-06502 [message #426257 is a reply to message #426250] Wed, 14 October 2009 10:32 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #426319 is a reply to message #426250] Wed, 14 October 2009 23:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
passing values from a cursor as you are doing is fine.

plsql numeric or value error means:

1) you are putting non-numbers into a number variable
OR
2) you are putting 10 bytes into a 5 bytes variable.

SQL> declare
  2     anumber_v number;
  3  begin
  4     anumber_v := 'a';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

SQL> declare
  2     astring_v varchar2(1);
  3  begin
  4     astring_v := '12';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

Figure out which one you are doing and fix it.

Good luck, Kevin
Re: ORA-06502 [message #426343 is a reply to message #426319] Thu, 15 October 2009 00:46 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: ORA: 14551 : cannot perform a DML operation inside a query (merged)
Next Topic: INSERT A RECORD
Goto Forum:
  


Current Time: Mon Nov 04 06:24:29 CST 2024