Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error
ORA-06502: PL/SQL: numeric or value error [message #200046] Fri, 27 October 2006 11:52 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Hi Friends!

I have problem with the following block of
a large procedure.

I have declare collection type on the table column
datatype of the tables, still i am facing
: numaric or value error
when try to iterate through.


Please guide to solve this.


..........................................
   TYPE authcreateddatetime_aat IS TABLE OF tblpatservprov.createddatetime%TYPE
      INDEX BY BINARY_INTEGER;

   l_authcreateddatetime       authcreateddatetime_aat;

   TYPE authorizationid_aat IS TABLE OF tblpatservprov.authorizationid%TYPE
      INDEX BY BINARY_INTEGER;

   l_authorizationid           authorizationid_aat;

   TYPE extracontractreasoncode_aat IS TABLE OF tblpatservprov.extracontractreasoncode%TYPE
      INDEX BY BINARY_INTEGER;

   l_extracontractreasoncode   extracontractreasoncode_aat;

BEGIN 

SELECT psp.patientnumber, psp.intakeid,
       u.operationcentercode opcenterprocessed, psp.servicecode, psp.uomcode,
       psp.startdt, psp.provid, psp.expdt, NVL (psp.units, 0) units,
       pas.descript, pas.servicecatid, psp.createdby authcreatedby,
       psp.createddatetime authcreateddatetime, psp.authorizationid,
       psp.extracontractreasoncode, pas.servicetypecode,
       NVL (psp.provnottoexceedrate, 0) provoverriderate,
       prov.shortname provshortname, psp.overridereasoncode,
       pas.contractprodclassid
BULK COLLECT INTO l_patientnumber, l_intakeid,
       l_opcenterprocessed, l_servicecode, l_uomcode,
       l_startdt, l_provid, l_expdt, l_units,
       l_descript, l_servicecatid, l_authcreatedby,
       l_authcreateddatetime, l_authorizationid,
       l_extracontractreasoncode, l_servicetypecode,
       l_provoverriderate,
       l_provshortname, l_overridereasoncode,
       l_contractprodclassid
  FROM tblpatservprov psp,
       tblproductsandsvcs pas,
       tblprov prov,
       tbluser u,
       tblglmonthlyclose glmc
 WHERE glmc.authorizationid >= v_startauthid
   AND glmc.authorizationid < v_stopauthid
   AND psp.authorizationid = glmc.authorizationid
   AND psp.authorizationid < v_stopauthid
   AND (psp.expdt >= v_fiscalstart OR psp.expdt IS NULL)
   AND psp.servicecode = pas.servicecode(+)
   AND prov.provid(+) = psp.provid
   AND u.userid(+) = psp.createdby;
   
   
    

for i in l_authorizationid.first..l_authorizationid.last loop   		

        v_AuthId := l_AuthorizationId(i);
   ........................................
................................................. 
............................................



I am facing the following error at this line.


=====> This line is 2140 <===========

for i in l_authorizationid.first..l_authorizationid.last loop   		


ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "AKDADHAN.PK_MONTHLY2_TEST1", line 2140



Re: ORA-06502: PL/SQL: numeric or value error [message #200068 is a reply to message #200046] Fri, 27 October 2006 14:07 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
try
if l_authorizationid.last  is not null then...
for i in l_authorizationid.first..l_authorizationid.last loop

or
for i in 1..l_authorizationid.count loop   

Re: ORA-06502: PL/SQL: numeric or value error [message #200082 is a reply to message #200068] Fri, 27 October 2006 15:41 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Thanks you, Andrew, for your reply.

I have tried the second option from your suggestion
it worked but inserted only 5 rows there are million
of rows.

In fact i have two procedure with defferent purpose
they both use the above loop same way and i tried your 2nd option both the place it was able to insert 5 rows only.
(both procedure processing million of rows)
what could be wrong?

I have not tried the first option because i really do not
understand the full IF statement i.e. how to write it .

Could you help me with that?

Thanking you.
Re: ORA-06502: PL/SQL: numeric or value error [message #200280 is a reply to message #200082] Mon, 30 October 2006 03:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I feel using the following is not a good practice.

for i in 1..l_authorizationid.count loop


As you can delete some of the elements in index by tables, and there will be gaps in between.

So I suggest to use the first one only but with a little modification.

if l_authorizationid.count <> 0 then
  for i in l_authorizationid.first..l_authorizationid.last loop
  <your statements>
  end loop;
end if;


If at all there is no data in the table, then l_authorizationid.first and l_authorizationid.last would be NULL's. So, for will not work properly. Hence the IF.

Hope I'm clear.

By
Vamsi.
Re: ORA-06502: PL/SQL: numeric or value error [message #200386 is a reply to message #200280] Mon, 30 October 2006 11:12 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks a lot, Vasmi.

I had figured it out it is working fine now.

Again Thanks for you effort.
Re: ORA-06502: PL/SQL: numeric or value error [message #516645 is a reply to message #200046] Tue, 19 July 2011 15:24 Go to previous message
pdr33n
Messages: 1
Registered: July 2011
Junior Member
Thats worked for me too! "if l_authorizationid.count <> 0 then"


Thanks!
Previous Topic: sql help
Next Topic: trigger - must be declared error
Goto Forum:
  


Current Time: Fri Dec 09 04:05:48 CST 2016

Total time taken to generate the page: 0.06139 seconds