Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Using PRAGMA restrict_references AND SYS_OP_GUID() Function

Re: Using PRAGMA restrict_references AND SYS_OP_GUID() Function

From: Mike Thys <mike.thys_at_crossfactory.net>
Date: Thu, 27 Jun 2002 10:27:58 +0200
Message-ID: <3d1ac949_2@corp.newsgroups.com>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:uhjv4641nu1788_at_corp.supernews.com...
>
> "Mike Thys" <mike.thys_at_crossfactory.net> wrote in message
> news:3d19ebab_2_at_corp.newsgroups.com...
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:uhjqmp11chlu03_at_corp.supernews.com...
> > >
> > > "Mike Thys" <mike.thys_at_crossfactory.net> wrote in message
> > > news:3d19dcd9_2_at_corp.newsgroups.com...
> > > > Hi Guys
> > > >
> > > > I have to generate some GUID (like MS Guid from sql server) from a
> > stored
> > > > procedure in oracle 8.0.5
> > > >
> > > > i have read lot of message saying that i should use select
SYS_GUID()
> > from
> > > > dual
> > > > when i do that i got this message
> > > >
> > > > >select SYS_GUID() from dual
> > > > > *
> > > > >ORA-00904: invalid column name
> > > >
> > > > after a deep search on the net, i have found the SYS_OP_GUID()
> function
> > > > that work perfectly when i do
> > > > select SYS_OP_GUID() from dual
> > > >
> > > > SYS_OP_GUID()
> > > > --------------------------------
> > > > 362CBB07A1A44DE39C08CF69ADF9C00A
> > > > 1 row selected.
> > > >
> > > > and that's exactly what i want... ;) BUT
> > > > i need to get the GUID in a variable... and
> > > > select SYS_OP_GUID() INTO MyVar from dual
> > > > doesn't work...
> > > >
> > > > so i try this
> > > >
> > > > /*___ Package declaration ___*/
> > > >
> > > > CREATE OR REPLACE PACKAGE Tools IS
> > > >
> > > > FUNCTION NewID return varchar2 ;
> > > > pragma restrict_references(NewID, WNDS);
> > > >
> > > > END Tools ;
> > > > /
> > > > show errors;
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY Tools IS
> > > >
> > > > FUNCTION NewID return varchar2 is
> > > > l_thecursor integer;
> > > > l_rows integer;
> > > > p_guid varchar2(32);
> > > > begin
> > > > l_thecursor := dbms_sql.open_cursor;
> > > > dbms_sql.parse( l_thecursor, 'select rawtohex(sys_op_guid())
from
> > > > dual',dbms_sql.native );
> > > > dbms_sql.define_column( l_thecursor, 1, p_guid, 32 );
> > > > l_rows := dbms_sql.execute_and_fetch( l_thecursor, true );
> > > > dbms_sql.column_value( l_thecursor, 1, p_guid );
> > > > dbms_sql.close_cursor( l_thecursor );
> > > >
> > > > return p_guid;
> > > > end;
> > > >
> > > > END Tools;
> > > > /
> > > > show errors;
> > > > /
> > > >
> > > > AND I GOT
> > > >
> > > > Errors for PACKAGE BODY TOOLS:
> > > > LINE/COL ERROR
> > >
> >
>
> --------------------------------------------------------------------------
> > > --
> > > > ----
> > > > 3/2 PLS-00452: Subprogram 'NEWID' violates its associated
pragma
> > > > 0/0 PL/SQL: Compilation unit analysis terminated
> > > > SQLWKS> /
> > > >
> > > > I cant figure me what's wrong except that SYS_OP_GUID does not
respect
> > the
> > > > pragma restrict_reference
> > > >
> > > > Could someone give me a hint on
> > > > - how to solve that problem
> > > > or
> > > > - how to get an GUID in a var...
> > > >
> > > >
> > > > Many thanks.
> > > >
> > > > Regards,
> > > >
> > > > Mike
> > > >
> > > >
> > > >
> > > >
> > > > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > > > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > > > -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
> > >
> > >
> > > You don't need dbms_sql
> > >
> > > Why don't you just use
> > >
> > > select rawtohex(sys_op_guid())
> > > into p_guid
> > > from
> > > dual;
> > >
> > > and check your pl/sql manual on the correct select into syntax.
> > >
> > >
> > > Consequently you also don't need the restric_references
> > > in fact you don't need the package at all.
> > >
> > > why don't you just provide the exact error message with your select
> into?
> > > Regards
> > >
> > >
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
> > >
> >
> > I do not use that sentence because of this error message
> >
> >
> > LINE/COL ERROR
>
> --------------------------------------------------------------------------
> --
> > ----
> > 8/17 PLS-00201: identifier 'SYS_OP_GUID' must be declared
> > 8/1 PL/SQL: SQL Statement ignored
> >
> > i do not understand why.
> >
> > Mike
> >
> >
> >
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
>
>
>
> You are running the Enterprise Edition?
> check
> select * from v$option
> for the 'Objects option'
> sys_op_guid seems to be an Oracle Objects thingy.
> If v$option list false, I have to disappoint you.
> In that case you don't have that function
>
> Hth
>
>
> --
> Sybrand Bakker

well... here is what i got

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production

SQLWKS> select * from v$option

     2>
PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------
Partitioning                                                     FALSE
Objects                                                          FALSE
Parallel Server                                                  FALSE
Advanced replication                                             TRUE
Bit-mapped indexes                                               TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE
Parallel load                                                    TRUE
Point-in-time tablespace recovery                                TRUE
14 rows selected.

SQLWKS> select SYS_OP_GUID() from dual

     2>
SYS_OP_GUID()



BBFF430B8C9B47A5AB3836869331756A
1 row selected.

seems that the funtion exist...

I'm lost...

Mike

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Thu Jun 27 2002 - 03:27:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US