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

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -