Home » SQL & PL/SQL » SQL & PL/SQL » using the SYS_GUID() function (Win 2000, 10g)
using the SYS_GUID() function [message #317843] Sun, 04 May 2008 03:56 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I am trying to assign a unique number to the primary key of my table by using the sys_guid() function but only the first letter of the sequence is being inserted, here is what I did

create table test (ID long raw default sys_guid(), name varchar2(10))

insert into test(name) values ('DAS');
insert into test(name) values ('DDAS');

Quote:

1 row created


select * from test


Quote:
I Name
6 DAS
E DDAS


Can anyone tell me how to insert the 32 digits?

- Das
Re: using the SYS_GUID() function [message #317845 is a reply to message #317843] Sun, 04 May 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SYS_GUID returns a RAW(16) why do you use a LONG RAW which will put in big troubles?
Don't you use NAME as column name as this is a reserved word and will put you in troubles.

SQL> create table test (ID raw(16) default sys_guid(), name varchar2(10))
  2  /

Table created.

SQL> insert into test(name) values ('DAS');

1 row created.

SQL> insert into test(name) values ('DDAS');

1 row created.

SQL> select * from test;
ID                               NAME
-------------------------------- ----------
CF874CB4F32A47C19859DA2BB0D71AB4 DAS
2B768E600A484391B6B8D5C4924D2785 DDAS

2 rows selected.

There is no problem with RAW(16). Never use LONG RAW.

Regards
Michel
Re: using the SYS_GUID() function [message #317848 is a reply to message #317845] Sun, 04 May 2008 04:25 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks a lot

- Das
Re: using the SYS_GUID() function [message #362053 is a reply to message #317845] Sun, 30 November 2008 05:13 Go to previous messageGo to next message
billkathj
Messages: 3
Registered: November 2008
Junior Member
I know this is not the main subject of this thread but actually NAME is NOT a reserved word, AFAIK.
Re: using the SYS_GUID() function [message #362058 is a reply to message #362053] Sun, 30 November 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select keyword from v$reserved_words where keyword='NAME';
KEYWORD
------------------------------
NAME

1 row selected.

It is. Upgrade your knowledge.

Regards
Michel

[Updated on: Sun, 30 November 2008 06:17]

Report message to a moderator

Re: using the SYS_GUID() function [message #362059 is a reply to message #362053] Sun, 30 November 2008 06:19 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Apparently, it is not. Here's the list of Oracle 10g's reserved words.

[Adding a few more lines after seeing Michel's reply]

SQL> desc v$reserved_words
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 KEYWORD                                            VARCHAR2(30)
 LENGTH                                             NUMBER
 RESERVED                                           VARCHAR2(1)
 RES_TYPE                                           VARCHAR2(1)
 RES_ATTR                                           VARCHAR2(1)
 RES_SEMI                                           VARCHAR2(1)
 DUPLICATE                                          VARCHAR2(1)

SQL> select * from v$reserved_words where keyword = 'NAME';

KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
NAME                                    4 N N N N N

SQL>
v$reserved_words lists all keywords. "Reserved" column's meaning is: a value of Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved.

As "reserved" column's value for the "NAME" keyword equals "N", it seems that "NAME" is a keyword, but is not reserved word.

[Updated on: Sun, 30 November 2008 06:23]

Report message to a moderator

Re: using the SYS_GUID() function [message #362060 is a reply to message #362059] Sun, 30 November 2008 06:22 Go to previous messageGo to next message
billkathj
Messages: 3
Registered: November 2008
Junior Member
I did refer to the documentation before my previous post, but I didn't look closely.
The true fact is that "NAME" is in V$RESERVED_WORDS but with RESERVED=N. That means it can be used as an identifier.
See here for full info.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2126.htm#REFRN30204

[Updated on: Sun, 30 November 2008 06:27]

Report message to a moderator

Re: using the SYS_GUID() function [message #362061 is a reply to message #362059] Sun, 30 November 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the precision Littlefoot.
Indeed it is "just" a keyword but as it it seems a bad idea to use it anyway.

Regards
Michel
Re: using the SYS_GUID() function [message #362062 is a reply to message #362060] Sun, 30 November 2008 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The true fact is that "NAME" is in V$RESERVED_WORDS but with RESERVED=N. That means it can be used as an identifier.


Not true.
For instance,
SQL> select reserved from v$reserved_words where keyword='USER';
R
-
N

1 row selected.

SQL> alter table t add (user varchar2(30));
alter table t add (user varchar2(30))
                   *
ERROR at line 1:
ORA-00904: : invalid identifier

Regards
Michel
Re: using the SYS_GUID() function [message #362063 is a reply to message #362062] Sun, 30 November 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More:
SQL> var user varchar2(30)
SQL> select * from emp where ename=:user;
select * from emp where ename=:user
                               *
ERROR at line 1:
ORA-01745: invalid host/bind variable name

Regards
Michel
Re: using the SYS_GUID() function [message #362065 is a reply to message #362062] Sun, 30 November 2008 06:36 Go to previous messageGo to next message
billkathj
Messages: 3
Registered: November 2008
Junior Member
OK, the USER case is because RES_SEMI = 'Y'.
Re: using the SYS_GUID() function [message #362067 is a reply to message #362065] Sun, 30 November 2008 08:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
billkathj wrote on Sun, 30 November 2008 13:36
OK, the USER case is because RES_SEMI = 'Y'.

No, the RES_SEMI does not explain the first example.
Don't trust Oracle, if it is in this view, don't use it.

Regards
Michel
Previous Topic: SQL based on number of count
Next Topic: Table Analysis
Goto Forum:
  


Current Time: Sun Dec 04 00:39:07 CST 2016

Total time taken to generate the page: 0.22129 seconds