sql - Oracle11g numeric overflow in for loop -
i have loop in pl/sql function like:
for in min..max loop
variables i, min, max declared numeric
in case min , max ofen large, range small, ie:
min = 3232236033 max = 3232236286
as see range ~256, values oracle throws numeric overflow error , stuck on how working.
how should iterate on values?
edit
ok, have working answer, using of loop of max/min diff, not possible loop through big values in oracle?
edit error retrieve is:
sql error: ora-01426: nadmiar numeryczny ora-06512: przy "ps.dhcp", linia 88 01426. 00000 - "numeric overflow" *cause: evaluation of value expression causes overflow/underflow. *action: reduce operands.
line 88 of code is:
for client_ip in min_host..max_host
min_host, max_host, client_ip result of inet_aton
(numeric representation of ip)
it seems problem comes being cast small number (which seems a fault of pl/sql), can change loop type:
a while loop works fine
set serveroutput on / declare min_val number; max_val number ; iterator number ; begin min_val := 3232236033 ; max_val := 3232236286 ; iterator := min_val; while iterator<=max_val loop dbms_output.put_line(iterator); iterator := iterator + 1; end loop ; end; /
from here: http://download.oracle.com/docs/cd/e11882_01/appdev.112/e17126/controlstatements.htm#babeffdc
for loop index
the index of loop statement implicitly declared variable of type integer local loop. statements in loop can read value of index, cannot change it. statements outside loop cannot reference index. after loop statement runs, index undefined. (a loop index called loop counter.)
in example 4-17, loop statement tries change value of index, causing error.
onwards this: http://download.oracle.com/docs/cd/b28359_01/appdev.111/b28370/loop_statement.htm
index_name
an undeclared identifier names loop index (sometimes called loop counter). scope loop itself; cannot reference index outside loop.
the implicit declaration of index_name overrides other declaration outside loop. refer variable same name, use label. see example 4-22, "referencing global variable same name loop counter".
inside loop, index treated constant: can appear in expressions, cannot assigned value.
thus though declare "index" in declare, not being used within loop , instead using implicitly created index (which seems have precision small needs)
Comments
Post a Comment