"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
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
Received on Wed Jun 26 2002 - 12:32:13 CDT
