Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using PRAGMA restrict_references AND SYS_OP_GUID() Function
"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 TRUE14 rows selected.
SQLWKS> select SYS_OP_GUID() from dual
2>
SYS_OP_GUID()
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