Using PRAGMA restrict_references AND SYS_OP

From: Mike Thys <mike.thys_at_crossfactory.net>
Date: Wed, 26 Jun 2002 17:39:09 +0200
Message-ID: <3d19dcd9_2@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! =----- Received on Wed Jun 26 2002 - 10:39:09 CDT

Using PRAGMA restrict_references AND SYS_OP_GUID() Function