Chetan Gaonker | 7f4bf74 | 2016-05-04 15:56:08 -0700 | [diff] [blame] | 1 | CREATE OR REPLACE FUNCTION msqlippool(user varchar2, pool varchar2) |
| 2 | RETURN varchar2 IS |
| 3 | |
| 4 | PRAGMA AUTONOMOUS_TRANSACTION; |
| 5 | ip_temp varchar2(20); |
| 6 | BEGIN |
| 7 | |
| 8 | -- If the user's pool is dynamic, get an ipaddress (oldest one) from the corresponding pool |
| 9 | |
| 10 | if pool = 'Dynamic' then |
| 11 | select framedipaddress into ip_temp from (select framedipaddress from radippool where expiry_time < current_timestamp and pool_name = pool ORDER BY expiry_time) where rownum = 1; |
| 12 | return (ip_temp); |
| 13 | |
| 14 | -- Else, then get the static ipaddress for that user from the corresponding pool |
| 15 | |
| 16 | else |
| 17 | select framedipaddress into ip_temp from radippool where username = user and pool_name = pool; |
| 18 | return (ip_temp); |
| 19 | end if; |
| 20 | |
| 21 | exception |
| 22 | |
| 23 | -- This block is executed if there's no free ipaddresses or no static ip assigned to the user |
| 24 | |
| 25 | when NO_DATA_FOUND then |
| 26 | if pool = 'Dynamic' then |
| 27 | return(''); -- so sqlippool can log it on radius.log |
| 28 | end if; |
| 29 | |
| 30 | -- Else, grabs a free IP from the static pool and saves it in radippool so the user will always get the same IP the next time |
| 31 | |
| 32 | select framedipaddress into ip_temp from (select framedipaddress from radippool where expiry_time < current_timestamp and username is null and pool_name = pool) where rownum = 1; |
| 33 | UPDATE radippool SET username = user where framedipaddress = ip_temp; |
| 34 | commit; |
| 35 | return (ip_temp); |
| 36 | |
| 37 | when others |
| 38 | then return('Oracle Exception'); |
| 39 | |
| 40 | END; |
| 41 | / |