OraFAQ Forum: SQL & PL/SQL » using the SYS

Home » SQL & PL/SQL » SQL & PL/SQL » using the SYS_GUID() function (Win 2000, 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

using the SYS_GUID() function [message #317843]

Sun, 04 May 2008 03:56

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

Report message to a moderator

Re: using the SYS_GUID() function [message #317845 is a reply to message #317843]

Sun, 04 May 2008 04:10

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: using the SYS_GUID() function [message #317848 is a reply to message #317845]

Sun, 04 May 2008 04:25

durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere

Senior Member

Thanks a lot

- Das

Report message to a moderator

Re: using the SYS_GUID() function [message #362053 is a reply to message #317845]

Sun, 30 November 2008 05:13

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.

Report message to a moderator

Re: using the SYS_GUID() function [message #362058 is a reply to message #362053]

Sun, 30 November 2008 06:16

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Littlefoot
Messages: 21826
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

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

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: using the SYS_GUID() function [message #362062 is a reply to message #362060]

Sun, 30 November 2008 06:33

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: using the SYS_GUID() function [message #362063 is a reply to message #362062]

Sun, 30 November 2008 06:34

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator