Unsigned integer to IP address?

[Monica Roman]

Hello everyone,
My question is how to upload an unsigned integer so that it looks like
an IP address in Oracle?
A Perl program captures IP addresses from a log server and puts them
into a .csv file that I then sqload into Oracle. When I query the db
the numbers look very different e.g., (just an example) 2423598587
instead of 134.290.34.59 (this was made up, any similarity to real IP
is pure coincidence).
I need them to look like an IP address.

Thank you much,

Monica

[Ed prochak]

mo*********@yahoo.com (Monica Roman) wrote in message news:<9e**************************@posting.google. com>...

Hello everyone,
My question is how to upload an unsigned integer so that it looks like
an IP address in Oracle?
A Perl program captures IP addresses from a log server and puts them
into a .csv file that I then sqload into Oracle. When I query the db
the numbers look very different e.g., (just an example) 2423598587
instead of 134.290.34.59 (this was made up, any similarity to real IP
is pure coincidence).
I need them to look like an IP address.

Thank you much,

Monica

How did you load it to get different values out versus what was
loaded? Your process obviously is flawed. IMHO you can store it in the
DB as either

a text field and load and use it as text

or

4 integer fields, one for each portion.

Those are the choices I see.

HTH
ed

[Ana C. Dent]

Monica Roman wrote:

Hello everyone,
My question is how to upload an unsigned integer so that it looks like
an IP address in Oracle?
A Perl program captures IP addresses from a log server and puts them
into a .csv file that I then sqload into Oracle. When I query the db
the numbers look very different e.g., (just an example) 2423598587
instead of 134.290.34.59 (this was made up, any similarity to real IP
is pure coincidence).
I need them to look like an IP address.

Thank you much,

Monica

Inside the OS an IP# is just a 32-bit integer.
It is typically represented in what is called "dottted-quad" notation.
This means that each 8-bit byte (0-255) represents one of the 4 numbers
displayed. So you need to "convert" the large decimal number into the
appropriate ONE & ZERO, group them into four 8 bits bytes & the convert
those four values back to decimal (Small Matter Of Programming [SMOP]).

[Monica Roman]

"Ana C. Dent" <an*******@hotmail.com> wrote in message news:<lGhgb.54981$Ms2.34316@fed1read03>...

Inside the OS an IP# is just a 32-bit integer.
It is typically represented in what is called "dottted-quad" notation.
This means that each 8-bit byte (0-255) represents one of the 4 numbers
displayed. So you need to "convert" the large decimal number into the
appropriate ONE & ZERO, group them into four 8 bits bytes & the convert
those four values back to decimal (Small Matter Of Programming [SMOP]).

Ana, Thank you! Now the question is how do I do that? Or, where can I
find info, any books or articles you can recommend?

Thanks,

Monica

[Ed prochak]

mo*********@yahoo.com (Monica Roman) wrote in message news:<9e**************************@posting.google. com>...

"Ana C. Dent" <an*******@hotmail.com> wrote in message news:<lGhgb.54981$Ms2.34316@fed1read03>...

Inside the OS an IP# is just a 32-bit integer.
It is typically represented in what is called "dottted-quad" notation.
This means that each 8-bit byte (0-255) represents one of the 4 numbers
displayed. So you need to "convert" the large decimal number into the
appropriate ONE & ZERO, group them into four 8 bits bytes & the convert
those four values back to decimal (Small Matter Of Programming [SMOP]).

Ana, Thank you! Now the question is how do I do that? Or, where can I
find info, any books or articles you can recommend?

Thanks,

Monica

Your initial quest was about loading it into the DB and then
displaying it later. Have you yet chosen what datatype to store it in:
VARCHAR, CHAR, INTEGER, BLOB?

You need the format for input via SQL*Loader
You need the format desired for display (this might be the default if
you choose the datatype well)
You may need the format for programs (PERL, C, PL/SQL) to use

The books I'd recommend are the oracle manuals. Basically your
questions are too vague to warrant a more precise answer.

Since you said you did get it to load some data but it did "look
right", how about showing us the code you've tried so far. That would
certainly show a little better what your problem is.

Ed

[Ana C. Dent]

Monica Roman wrote:

"Ana C. Dent" <an*******@hotmail.com> wrote in message news:<lGhgb.54981$Ms2.34316@fed1read03>...

Inside the OS an IP# is just a 32-bit integer.
It is typically represented in what is called "dottted-quad" notation.
This means that each 8-bit byte (0-255) represents one of the 4 numbers
displayed. So you need to "convert" the large decimal number into the
appropriate ONE & ZERO, group them into four 8 bits bytes & the convert
those four values back to decimal (Small Matter Of Programming [SMOP]).

Ana, Thank you! Now the question is how do I do that? Or, where can I
find info, any books or articles you can recommend?

Thanks,

Monica

PERL has a built-in function ( but I don't recall its name right now)
which "converts" the integer into a dotted-quad string; complete with
three decimal points in the correct places.

[Ethel Aardvark]

In no particular language (i.e., you may need FLOOR() instead of INT())...

To convert dotted (A.B.C.D) to a number:
D
+ 256 * C
+ 256 * 256 * B
+ 256 * 256 * 256 * A

To convert number (X) back to dotted:
temp = X / 256
D = 256 * (temp - INT(temp))
temp = (INT (temp)) / 256
C = 256 * (temp - INT(temp))
temp = (INT (temp)) / 256
B = 256 * (temp - INT(temp))
A = INT (temp)

(There is a chance I have the endian-ness wrong,
in which case swap A<->D and B<->C in the calculations.)

Regards,

ETA

"Ana C. Dent" <an*******@hotmail.com> wrote in message news:<lGhgb.54981$Ms2.34316@fed1read03>...

Monica Roman wrote:

Hello everyone,
My question is how to upload an unsigned integer so that it looks like
an IP address in Oracle?
A Perl program captures IP addresses from a log server and puts them
into a .csv file that I then sqload into Oracle. When I query the db
the numbers look very different e.g., (just an example) 2423598587
instead of 134.290.34.59 (this was made up, any similarity to real IP
is pure coincidence).
I need them to look like an IP address.

Thank you much,

Monica

Inside the OS an IP# is just a 32-bit integer.
It is typically represented in what is called "dottted-quad" notation.
This means that each 8-bit byte (0-255) represents one of the 4 numbers
displayed. So you need to "convert" the large decimal number into the
appropriate ONE & ZERO, group them into four 8 bits bytes & the convert
those four values back to decimal (Small Matter Of Programming [SMOP]).