RE: dynamic pkg execution

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 25 Jan 2001 09:12:31 -0800
Message-Id: <10752.127511@fatcity.com>

Oracle 8i introduced the "trusted". This argument says that you trust the called functions not to violate the purity level.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
From: Gabriel Galanternik [mailto:ggalanterni_at_tesis-oys.com.ar] Sent: Thursday, January 25, 2001 6:46 AM To: Multiple recipients of list ORACLE-L Subject: RE: dynamic pkg execution

mmmmhhhhh, I bet you can't
to use the function in select clause of a statement the function must be a
"pure" one.

in select clause the function must guarantee not to update database (WNDS), not to
update package (WNPS).
As the purity level of a function is the "minor" purity level of its own code plus the
functions or procedures called by it, and as the dbms_sql package is so impure,
your function will never suffice the purity level needed.

I wonder what do you mean with
"This works fine on AUTONOMOUS_TRANSACTIONS."
anyway, it won't work in select statement. if you use it, first you will get error saying "Function ... does not guarantee not to update database"
it means that packaged function must declare its purity level, so you must use the instruction

   Pragma restrict_references (your_function, your purity levels) in the package spec.
but in your case, when you add the instruction and use the function you will get the error:
"function... violates its associated pragma"

I heard oracle added some pragma definition to its own packages (dbms_sql, for example) in
some 8i version but I don't know. you could check it out. 8.0 and previous, I think I'm ok.
hth
Gabriel Galanternik

• Original Message ----- To: Multiple recipients of list ORACLE-L Sent: Thursday, January 25, 2001 4:55 AM

Hi all,
I want to execute a list of functions in dynamic sql.

The list of the functions is stored in a table. With a cursor I get each function and execute it in a statement like this:

declare

     cursor c_job_list select job_no, command from t_cmd; begin

     for r_job in c_job_list
     loop
            v_cmd:='select '||r_job.command||'('||r_job.Job_no||') from

dual';

            dbms_sql.parse(v_dyn, v_cmd, DBMS_SQL.NATIVE );

            dbms_sql.define_column(v_dyn,1,v_retVal);
            v_back:=dbms_sql.EXECUTE(v_dyn);
            loop
                 exit when dbms_SQL.FETCH_ROWS (v_dyn)=0;
                 dbms_sql.COLUMN_VALUE(v_dyn,1, v_retVal);
                 dbms_output.put_line(to_char(v_retVal));
            end loop;
     end loop

end;

This works fine on AUTONOMOUS_TRANSACTIONS.

My question is:
can I execute the function on a other way like select pkg.fk from dual ??

Wolfgang Ludewig

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gabriel Galanternik
  INET: ggalanterni_at_tesis-oys.com.ar

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Thu Jan 25 2001 - 11:12:31 CST