Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL error Ora-06502 (Oracle 11g, Reports builder 6i)
PL/SQL error Ora-06502 [message #673071] Mon, 05 November 2018 02:32 Go to next message
kmack
Messages: 2
Registered: November 2018
Junior Member
I am getting ORA-06502 PL/SQL numeric or value error from the below select statement in reports builder 6i

select to_char(to_date(lateby, 'sssss'), 'mi:ss')
from attendancelogs
where emp_no = :emp_no
and attendancedate = :dat;

late number(15);

The same statement when run under SQL*Plus is running fine without any error and fetching values the way I want.

The field lateby in the attendancelogs table is defined as number(10). It is an integer value having 3 digits from 0-999 and i am coverting this integer to give me hours:minutes:seconds.

I am using Reports builder 6i and Oracle 11g database.

Any help will be much much appreciated x 1000 times.
Re: PL/SQL error Ora-06502 [message #673074 is a reply to message #673071] Mon, 05 November 2018 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i am coverting this integer to give me hours:minutes:seconds.
Quote:
select to_char(to_date(lateby, 'sssss'), 'mi:ss')

"Hour" is missing in your format.
Anyway, if your value is really less than 1000 it should work (as 1000 is less than 1 hour) and it can't return this error.

Maybe, and most surely, imo, the problem does not come from this part but from the "where" clause.

Re: PL/SQL error Ora-06502 [message #673075 is a reply to message #673074] Mon, 05 November 2018 03:10 Go to previous messageGo to next message
kmack
Messages: 2
Registered: November 2018
Junior Member
Sorry, Yes i am getting only minutes and seconds and hours is not important to me..
Where do you think the error is where clause is ...it is working just fine when i run the same statment in sql *..
Please advise
Re: PL/SQL error Ora-06502 [message #673078 is a reply to message #673075] Mon, 05 November 2018 05:10 Go to previous message
Michel Cadot
Messages: 66474
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Most likely, variables are not defined in the same way or environment variables are not the same ones.

Previous Topic: Ranking data by a set of data maybe using analytical function
Next Topic: ORA-00904 on selecting by column name
Goto Forum:
  


Current Time: Fri Jul 19 09:29:21 CDT 2019