Re: Using PRAGMA restrict_references AND SYS_OP_GUID() Function

"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! =----- Received on Wed Jun 26 2002 - 11:37:15 CDT