| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
Seems that it's just not callable from PL/SQL on 8.0.5. Anyway, you don't
need dbms_sql here, you can just
create or replace function get_guid return varchar2 as
rv varchar2(32);
begin
execute immediate 'select rawtohex(sys_op_guid()) from dual' into rv;
return rv;
and then use it in selects or PL/SQL alike:
select get_guid from dual;
declare
l_guid varchar2(32) := get_guid;
...
No guarantee it will work on 8.0.5 though - I only checked it on 8.1.6 as this is the oldest version I have access to...
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Mike Thys" <mike.thys_at_crossfactory.net> wrote in message news:3d1ac949_2_at_corp.newsgroups.com...Received on Thu Jun 27 2002 - 10:25:45 CDT
>
> "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! =-----
 |
 |