Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error
ORA-06502: PL/SQL: numeric or value error [message #222746] Tue, 06 March 2007 05:12 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
create or replace procedure ddd as
cursor c1 is select count(regno) as a,remarks as r from
(
SELECT REGNO, upper(rpad(name,40)) as name,faculty,SGPA,cgpa, MAX (PROBATION) as remarks
  FROM   (select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation1' AS PROBATION from it
        where sc=1 and sy=2006
        and sgpa<=1.995
        UNION ALL
        select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation2' AS PROBATION from it
       where sc=1 and sy=2006
        and sgpa<=1.995
        and regno in(
        select regno from it
        where sc=3 and sy=2005
        and sgpa<=1.995)
        UNION ALL
        select regno,sc,sy,round(sgpa,2) AS SGPA,round(cgpa,2) AS CGPA, 'Probation3' AS PROBATION from it
      where sc=1 and sy=2006
        and sgpa<=1.995
        and regno in(
        select regno from it
        where sc=3 and sy=2005
        and sgpa<=1.995
       and regno in(
        select regno from it
        where sc=1 and sy=2005
        and sgpa<=1.995))) gg, grn
where grn.rn=gg.regno
  GROUP  BY REGNO,name,faculty, SGPA, cgpa)
group by remarks;
begin
for x in c1 loop
dbms_output.put_line(x.r);
end loop;
null;
end;
/

Procedure created.

EX > set serveroutput on
EX > exec ddd;
BEGIN ddd; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_OUTPUT", line 57
ORA-06512: at "EX.DDD", line 33
ORA-06512: at line 1


EX > 



Please help me anybody ..

RzKhan
Re: ORA-06502: PL/SQL: numeric or value error [message #222749 is a reply to message #222746] Tue, 06 March 2007 05:18 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Add a dbms_output.enable(size) to your procedure.

Look at below link for a description
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_outpu2.htm#1000413
Re: ORA-06502: PL/SQL: numeric or value error [message #222750 is a reply to message #222746] Tue, 06 March 2007 05:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are the datatypes of the columns SC, SY and SGPA
Re: ORA-06502: PL/SQL: numeric or value error [message #222753 is a reply to message #222750] Tue, 06 March 2007 05:37 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Adding line
dbms_output.enable(2000);
has no effect.

the data type of SC, SY, SGPA is number. remarks is varchar2

RzKhan
Re: ORA-06502: PL/SQL: numeric or value error [message #222764 is a reply to message #222753] Tue, 06 March 2007 05:49 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Quote:

cursor c1 is select count(regno) as a, rpad(remarks,50) as r from



By adding rpad function... my problem has been solved. Thank you All...

The result is as below...

EX > exec ddd;
Probation1                                         76
Probation2                                         58
Probation3                                         7

PL/SQL procedure successfully completed.

EX > 


RzKhan
Re: ORA-06502: PL/SQL: numeric or value error [message #222765 is a reply to message #222764] Tue, 06 March 2007 05:51 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
ORA-06502: PL/SQL: numeric or value error string 
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). 
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. 




RzKhan
Re: ORA-06502: PL/SQL: numeric or value error [message #222769 is a reply to message #222765] Tue, 06 March 2007 05:56 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
tnx for your feedback.
I now realize (again) that a lot of problems have an easy/simple solution. You just have to look at the right place.
Previous Topic: stored procedure
Next Topic: escape charecter
Goto Forum:
  


Current Time: Wed Dec 07 22:06:51 CST 2016

Total time taken to generate the page: 0.18820 seconds