Storing an IP Address in a Oracle SQL Table

Question

I have created a table to store IP addresses.

CREATE TABLE ipdetails( ip_address DECIMAL(16,4) NOT NULL, vlan_id varchar(50) );

But when i try to insert into the table it gives me an error :

INSERT INTO ipdetails VALUES (192.169.165.128, 'Sample1')

Is there anyway i could store a number with lot of decimal places in a SQL table, if so what is the Data Type i should use? Please advice.

Thanks in Advance!

A valid IP address cannot contain parts that are greater than `255` (it's a 32-bit value). You cannot have `698` or `365` there. — Sergey Kalinichenko, Feb 21 '13 at 03:44

score 5 · Accepted Answer · answered Feb 21 '13 at 03:44

5

You have several options:

Store it as a VARCHAR. IP address isn't really a number you're likely to do math on, so why store it in numeric format?
Store it in hexidecimal as a single number. While you can't really do that in decimal, in hexidecimal an IP is an 8 digit number.
Store it as (up to) four separate fields. Probably unnecessary but for certain applications (where you might want to primarily only be concerned with one part of the IP) this could be useful.

answered Feb 21 '13 at 03:44

Joe

62,789
6
49
67

Thanks for the reply, i just want to know if there is a Data Type in SQL to store a number with lot of decimal places? – Chamara Keragala Feb 21 '13 at 03:47
1

@frozenhaart There is unfortunately no such intrinsic datatype. There isn't such a thing as a numeric datatype with more than one decimal point. An IP Address is really a compound "number". Each storage option has drawbacks like sorting semantics and range querying. – Cade Roux Feb 21 '13 at 03:51
2

The periods in an IP address aren't decimals. They're just field delimiters; it's just as legal to write `192:168:213:153` instead of with dots. – Joe Feb 21 '13 at 03:53
ok.. thanks for your clarifications! – Chamara Keragala Feb 21 '13 at 03:58

score 2 · Answer 2 · answered Feb 21 '13 at 03:49

You can store an IP as a number, that is how they exist internally, but you will have to write code to convert back and forth:

#include <arpa/inet.h>
/* 
 * Returns a pointer to an internal array containing the string, which is overwritten with each call to the function.
 * You need to copy the string if you want to keep the value returned.
 */
extern char *inet_ntoa (struct in_addr in);

/* 
 * Convert Internet host address from numbers-and-dots notation in CP
 * into binary data and store the result in the structure inp.
 */
extern int inet_aton (const char *cp, struct in_addr *inp);

Here is some simple SQL that does what one of these do ip->decimal , using 127.0.0.1

SELECT
TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,1))*POWER(2,24)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,2))*POWER(2,16)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,3))*POWER(2,8)
+ TO_NUMBER(REGEXP_SUBSTR('127.0.0.1','\w+',1,4))*POWER(2,0) IP
FROM
DUAL;

score 1 · Answer 3 · answered Feb 21 '13 at 03:44

Try storing the value as a string and using quotes:

CREATE TABLE ipdetails( ip_address varchar(15) NOT NULL, vlan_id varchar(50) );

and then . . .

INSERT INTO ipdetails VALUES ('192.169.698.365', 'Sample1')

You might want to store each component in a separate field.

Storing an IP Address in a Oracle SQL Table

3 Answers3

Linked