Umm, well, maybe it will not work - I found a reference that says that
execute immediate is only available since 8i. So you may have to resort
to dbms_sql after all. I believe the reason for getting pragma violation
error is dbms_sql - since it executes statements dynamically at runtime,
compiler cannot determine if executed statement will write package or
database state, and assumes it will (since it can), thus you get the error.
It does not parse and verify the statement being executed - it's just a
string from compiler's point of view, not a statement.
Try moving this code into a standalone function and see if it will work.
--
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.
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:affapt$2s3$1_at_babylon.agtel.net...
> 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;
> end;
>
> 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.
>
>
Received on Thu Jun 27 2002 - 10:33:09 CDT
