Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Werner.

Asked: September 23, 2017 - 9:48 am UTC

Answered by: Connor McDonald - Last updated: September 25, 2017 - 3:36 am UTC

Category: Database Development - Version: 5.1

Viewed 1000+ times

You Asked

Hi Tom

I read some articles about to_number(sys_guid()), but You suggest not to use it.

Why Apex QuickSQL suggests to_number(sys_guid()) for unique object-ID ?

create or replace trigger country_biu
before insert or update
on country
for each row
if is null then := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end country_biu;


and we said...

I'm not a fan, but I didn't write Quick SQL :-)

The reasons I'm not a fan are this:

1) You can't guaranteee a guid (as a number) will fit into NUMBER(38), which means you have to use NUMBER, which is going to be 21 bytes every row

SQL> insert into t values ( to_number(rawtohex(sys_guid()),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') );

1 row created.

SQL> select dump(x) from t;

Typ=2 Len=21: 212,2,45,61,17,62,63,93,46,95,2,9,15,8,7,61,11,93,44,13,28

2) I dont like triggers for default values, I would always opt for just using DEFAULT if possible. So if I *was* going for GUID's it would be DEFAULT not a trigger

3) An update trigger worries me - if I try update a primary key to null, I'd rather than be an error condition rather than just assign a new one

4) SYS_GUID can be potentially costly for high volume insertion environments

SQL> insert into t select seq.nextval from dual connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:00.18
SQL> insert into t select to_number(rawtohex(sys_guid()),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') from dual connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:02.79

Having said that, I know a lot of people use GUID's ... that's just their preference. In Oracle if I was going that way, I'd store them RAW not as a NUMBER, because they are a raw value.

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review


I agree

September 24, 2017 - 3:44 pm UTC

Reviewer: Werner Schwienbacher from Italy

I'm not using sys_guid to number, but I was wondering that Apex as an officiale Oracle part makes such as examples.

Connor McDonald


September 25, 2017 - 3:36 am UTC

Not to my knowledge.

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.