Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math? -- J.D. Falk <jdfalk@returnpath.net> Return Path Inc
Not in Oracle, but PostgreSQL has a very robust implementation for CIDR, including not only datatypes but also a host of operators to deal with them. Being opensource, it always seemed plausible to me to port the functionality into Oracle, learning from their implementation. Never got to actual development, though. hth, cl. On Fri, Mar 12, 2010 at 2:13 PM, J.D. Falk <jdfalk-lists@cybernothing.org>wrote:
Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
-- J.D. Falk <jdfalk@returnpath.net> Return Path Inc
On 3/12/2010 09:13, J.D. Falk wrote:
Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
Not exactly sample code, but: I do that with MySQL by storing the IP as its integer value and using simple comparisons to see if that stored value is within the range of values that a given CIDR mask represents. Works great for IPv4 and IPv6 addresses. ~Seth
Seth Mattinen wrote:
On 3/12/2010 09:13, J.D. Falk wrote:
Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
Not exactly sample code, but: I do that with MySQL by storing the IP as its integer value and using simple comparisons to see if that stored value is within the range of values that a given CIDR mask represents. Works great for IPv4 and IPv6 addresses.
~Seth
I do it in MySQL by storing the IP as an integer and the mask as an integer and using bitwise operators in the SELECT. Just something to think about... Matthew Kaufman
On Mar 12, 2010, at 9:36 PM, Matthew Kaufman wrote:
Seth Mattinen wrote:
On 3/12/2010 09:13, J.D. Falk wrote:
Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
Not exactly sample code, but: I do that with MySQL by storing the IP as its integer value and using simple comparisons to see if that stored value is within the range of values that a given CIDR mask represents. Works great for IPv4 and IPv6 addresses.
~Seth
I do it in MySQL by storing the IP as an integer and the mask as an integer and using bitwise operators in the SELECT.
Just something to think about...
To expand upon this, we do this in pure SQL as Matthew suggested by generating the sql automatically. e.g., to find all routes in a BGP table that equal or contain a particular prefix: SELECT * from table WHERE (prefix = x AND mask=32) OR (prefix = x & 0xfffffffe AND mask=31) OR ... (we store BGP entries as prefix, mask). You can write a user defined function to do this for you in many languages. We chose that expansion because it worked well with the indexes defined on prefix/mask. You can also express it as CIDR math as bitwise operators, though we've found that doing so tends to destroy any indexing you've created: SELECT mask, prefix from t1 WHERE (search_prefix & ((!0) << (32 - t1.mask)) = t1.prefix) ... I can probably cough up a few more examples from our codebase if you want them. -Dave
participants (5)
-
Claudio Lapidus -
David Andersen -
J.D. Falk -
Matthew Kaufman -
Seth Mattinen