Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SYS_OP_GUID
On Wed, 10 Mar 1999 16:56:12 +0100, "Luc Gyselinck"
<Luc.Gyselinck_at_be.pwcglobal.com> wrote:
>Oracle 8:
>
>I want to use GUID's as unique identifiers for tables.
>
>There exists a function(?) SYS_OP_GUID that generates
>a GUID (like the CoCreateGuid in Microsofts OLE/COM).
>
>It can be used as
>select SYS_OP_GUID() from DUAL;
>
>But I could not figure out how to use it in PL/SQL.
>
You are correct in that SYS_OP_GUID is not recognized in PL/SQL.
You could write a stored procedure that uses dynamic SQL and returns this value for you. As an example:
create or replace procedure myguid( p_val in out varchar2 ) is
l_thecursor integer;
l_rows integer;
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_val, 50 );
l_rows := dbms_sql.execute_and_fetch( l_thecursor, true );
dbms_sql.column_value( l_thecursor, 1, p_val );
dbms_sql.close_cursor( l_thecursor );
end;
Then, you could now call this from within PL/SQL:
SQL> declare
2 id varchar2(50);
3 begin
4 myguid( id );
5 dbms_output.put_line( 'ID: ' || id );
6 end;
7 /
ID: 45BD0053CA76CE95E030028A3D540A1A
PL/SQL procedure successfully completed.
>Thanx
>
>
Thanks!
Joel
Joel R. Kallman Oracle Service Industries
Columbus, OH jkallman@us.oracle.com http://www.oracle.com