"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
to reply remove '-verwijderdit' from my e-mail address
Received on Wed Jun 26 2002 - 11:22:00 CDT
