RE: dynamic pkg execution

From: Gabriel Galanternik <ggalanterni_at_tesis-oys.com.ar>
Date: Thu, 25 Jan 2001 11:46:11 -0300
Message-Id: <10752.127477@fatcity.com>

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 ----- From: wolfgang.ludewig_at_systor.com To: Multiple recipients of list ORACLE-L Sent: Thursday, January 25, 2001 4:55 AM Subject: dynamic pkg execution

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 ?? Received on Thu Jan 25 2001 - 08:46:11 CST