plsql - oracle datatype for multiple periods in number -

i looking if there number type datatype available storing number hierarchy like
1.0
1.0.1
1.0.2
2.0
2.0.1 etc in oracle table.
varchar2 option?

this question little old, here's take:

since you're looking combine both storage of multiple bits of data (the component portions of version number) along behavior (knowing how sort), user-defined type may need:

create or replace type version_num object (   major_version integer,   minor_version integer,   fix_version   integer,   order member function compare (other version_num) return integer ); /  create or replace type body version_num    order member function compare (other version_num) return integer   begin     if (self.major_version > other.major_version)       return 1;     elsif (self.major_version < other.major_version)       return -1;     else       if (self.minor_version > other.minor_version)         return 1;       elsif (self.minor_version < other.minor_version)         return -1;       else         if (self.fix_version > other.fix_version)           return 1;         elsif (self.fix_version < other.fix_version)           return -1;         else           return 0;         end if;       end if;     end if;   end compare; end; / 

first, creates type spec, containing (in example) 3 components of version number. important thing note order member function, provides oracle method sort version_num objects.

the second section tells oracle how perform sort. compares each component piece starting highest order, , returns -1 if self lower, +1 if self higher, or 0 if self equal other.

documentation on oracle user-defined functions here.

---

for demonstration purposes, here's simple use-case , test of our version_num object:

create table version_control (   sys_name varchar2(30),   version  version_num );  insert version_control (sys_name, version) values ('system one', version_num (9,8,0)); insert version_control (sys_name, version) values ('system two', version_num (9,9,0)); insert version_control (sys_name, version) values ('system three', version_num (9,9,5)); insert version_control (sys_name, version) values ('system four', version_num (9,10,0)); insert version_control (sys_name, version) values ('system five', version_num (9,11,0)); insert version_control (sys_name, version) values ('system six', version_num (10,0,0)); commit;  select * version_control v order v.version; 

since we've made user-defined type, can use datatype our table definitions else.

this solution can made more user-friendly addition of display_name property on version_num object, generated other properties via trigger. implement null handling when don't have 3 version components.