Re: software version number datatype

JimStreh..._at_data911.com wrote:
> Should Oracle consider creating a new datatype to handle data in the
> format '###.###.###.###' such as i.p. addresses and software version
> numbers?
> What sort of datatype would you call it if we could create a new data
> type?
> e.g. VERSION
>
> You can not use NUMBER format because of the multiple periods.
> Programmers currently use VARCHAR2; but you get situations where
> programmers forget and store '9.2.0.1' and '10.0.0.1' where ten-dot
is
> less than nine-dot (wrong).
>
> What is the "best" data type for storing the information at present?
> Is that a good use for a custom object? How would you store the
object
> into the database?
>
> Do you have a PL/SQL stored procedure or function to parse and clense
> such data in your own internal applications as a work-around the
> current lack of a new data type?
>
> Thanks in advance.

Instead of asking Oracle to invent a new datatype to support your logic... you could store the version, IP address, SS# etc as xxxx.xxxx.xxxx.xxxx.xxxx in varchar2 and write a simple PLSQL function using INSTR and SUBSTR to parse it out. You can do the mathematical comparison once you extract the values between the dots. e.g. between 9.2.0.1 and 10.0.0.1... compare the first pair in version between 9 and 10, then next pair between 2 and 0 and so on.

Not sure what is the logic behind doing a mathematical comparison between IP addresses or SS#'s.

Regards
/Rauf Received on Tue Apr 26 2005 - 18:57:15 CDT