Home » SQL & PL/SQL » SQL & PL/SQL » Question about sys_guid (11.2.0.2 on Solaris 10)
Question about sys_guid [message #564486] Fri, 24 August 2012 05:09 Go to next message
nirav_hyd
Messages: 724
Registered: December 2005
Location: Hyderabad
Senior Member
Hi experts,
I have a question on sys_guid function. I want to use it to generate and store primary key values which is stored in a table as varchar2 data. What I read in the documentation is that sys_guid() function returns a raw value of up to 16 bytes. Now I don't want to define the column as RAW but as VARCHAR2. So for this need, can I directly use the output of sys_guid to insert in a VARCHAR2 column or should I use RAWTOHEX function to convert it and then use that value?

For example:
SQL> create table t (c1 varchar2(32));
 
Table created.
 
SQL> insert into t values(sys_guid());  --This is WRONG because I am inseting raw data into varchar2 column , am I right?
1 row created.
 
 
 
 SQL> insert into t values(rawtohex(sys_guid()));   --this is the right way to insert the data , correct? 
 
1 row created.
 
SQL > commit;
 
Commit complete.
 
SQL > select * from t;
 
C1
--------------------------------
C800DC90BC14936BE040050A749536C1
C800DC90BC15936BE040050A749536C1
 
SQL>




Re: Question about sys_guid [message #564490 is a reply to message #564486] Fri, 24 August 2012 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't explicitly call the function Oracle will use it anyway behind the scene to convert from RAW to VARCHAR2.

Regards
Michel

[Updated on: Fri, 24 August 2012 05:59]

Report message to a moderator

Re: Question about sys_guid [message #564495 is a reply to message #564490] Fri, 24 August 2012 06:25 Go to previous messageGo to next message
nirav_hyd
Messages: 724
Registered: December 2005
Location: Hyderabad
Senior Member
Thanks a lot Michel!! Since yesterday I was puzzled by this issue and here I got the answer!! Thank you. Also curious that how did you figure it out? Is it like an implicit conversion?

Best regards,
Nirav
Re: Question about sys_guid [message #564496 is a reply to message #564495] Fri, 24 August 2012 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58477
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is indeed an implicit conversion.
Database SQL Reference
Implicit Data Conversion
2th column, 17th line

Regards
Michel
Re: Question about sys_guid [message #564497 is a reply to message #564496] Fri, 24 August 2012 06:32 Go to previous message
nirav_hyd
Messages: 724
Registered: December 2005
Location: Hyderabad
Senior Member
Simply Awesome!!
Previous Topic: Calculating leaves
Next Topic: To handle the exception and continue the process
Goto Forum:
  


Current Time: Tue Jul 22 02:21:39 CDT 2014

Total time taken to generate the page: 0.13223 seconds