RE: dynamic pkg execution

From: Gabriel Galanternik <ggalanterni_at_tesis-oys.com.ar>
Date: Fri, 26 Jan 2001 15:15:14 -0300
Message-Id: <10753.127625@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_006A_01C087AA.C8AACA00 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

i've read about it, but I didn't test.
anyway I think that if the function does really violates the pragma, = there's nothing you could do.
The trusted, I think, it's useful when you didn't or you can't define = the pragma in programs you are
using in your own.

Gabriel Galanternik

Original Message -----=20 From: MacGregor, Ian A.=20 To: Multiple recipients of list ORACLE-L=20 Sent: Thursday, January 25, 2001 2:11 PM Subject: RE: dynamic pkg execution

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-----
Sent: Thursday, January 25, 2001 6:46 AM To: Multiple recipients of list ORACLE-L

  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:=3D'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:=3Ddbms_sql.EXECUTE(v_dyn);
              loop
                   exit when dbms_SQL.FETCH_ROWS (v_dyn)=3D0;
                   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

  --=20
  Please see the official ORACLE-L FAQ: http://www.orafaq.com   --=20
  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   also send the HELP command for other information (like subscribing).   --=20
  Please see the official ORACLE-L FAQ: http://www.orafaq.com   --=20
  Author: MacGregor, Ian A.
    INET: ian_at_SLAC.Stanford.EDU

  Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
  San Diego, California        -- Public Internet access / Mailing Lists
  --------------------------------------------------------------------

------=_NextPart_000_006A_01C087AA.C8AACA00 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2014.210" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#0eecf1>
<DIV><FONT color=3D#0000ff>i've read about it, but I didn't =

test.</DIV>
<DIV>anyway I think that if the function does = really=20
violates the pragma, there's nothing you could do.</DIV> <DIV>The trusted, I think, it's useful when you = didn't or=20
you can't define the pragma in programs you are</DIV>

<DIV><FONT color=3D#0000ff>using in your own.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT color=3D#0000ff>Gabriel Galanternik</FONT></DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE=20

style=3D"BORDER-LEFT: #0000ff 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20
 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black">From:=20
 <A href=3D"mailto:ian_at_SLAC.Stanford.EDU" = title=3Dian_at_SLAC.Stanford.EDU>MacGregor,=20 Ian A.</A> </DIV>
 <DIV style=3D"FONT: 10pt arial">To: <A = href=3D"mailto:ORACLE-L_at_fatcity.com"=20
 title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> = </DIV>
 <DIV style=3D"FONT: 10pt arial">Sent: Thursday, January 25, = 2001 2:11=20
 PM</DIV>
 <DIV style=3D"FONT: 10pt arial">Subject: RE: dynamic pkg = execution</DIV>
 <DIV> </DIV>Oracle 8i introduced the "trusted".   This = argument=20
 says that you trust the called functions not to violate the purity=20 level. Ian MacGregor Stanford Linear Accelerator = Center <A=20
 =
href=3D"mailto:ian_at_slac.stanford.edu">ian_at_slac.stanford.edu</A> <B= R>-----Original=20
 Message----- Sent: Thursday, January 25, 2001 6:46 AM To: = Multiple=20
 recipients of list ORACLE-L mmmmhhhhh, I bet you = can't to use=20
 the function in select clause of a statement the function must be = a "pure"=20
 one. in select clause the function must guarantee not to update = database=20
 (WNDS), not to update package (WNPS). As the purity level of = a=20
 function is the "minor" purity level of its own code plus = the functions=20
 or procedures called by it, and as the dbms_sql package is=20 so impure, your function will never suffice the purity level=20 needed. I wonder what do you mean with "This works fine on=20 AUTONOMOUS_TRANSACTIONS." anyway, it won't work in select = statement. if=20
 you use it, first you will get error saying "Function ... does=20 not guarantee not to update database" it means that packaged = function=20
 must declare its purity level, so you must use the=20 instruction    Pragma restrict_references (your_function, = your=20
 purity levels) in the package spec. but in your case, when you = add the=20
 instruction and use the function you will get the = error: "function...=20
 violates its associated pragma" I heard oracle added some = pragma=20
 definition to its own packages (dbms_sql, for example) in some = 8i=20
 version but I don't know. you could check it out. 8.0 and = previous, I think=20
 I'm ok. hth Gabriel Galanternik ----- Original = Message=20
 ----- To: Multiple recipients of list ORACLE-L Sent: Thursday, = January=20
 25, 2001 4:55 AM Hi all, I want to execute a list of = functions=20
 in dynamic sql. The list of the functions is stored in a table. = With a=20
 cursor I get each function and execute it in a statement like=20 this: declare      cursor c_job_list = select=20
 job_no, command from t_cmd; begin      for = r_job in=20
 c_job_list     =20 =
loop           &nbsp= ;=20
 v_cmd:=3D'select '||r_job.command||'('||r_job.Job_no||')=20 =
from dual';         &nb= sp;  =20
 dbms_sql.parse(v_dyn, v_cmd, DBMS_SQL.NATIVE=20 =
);           &nb= sp;=20
 =
dbms_sql.define_column(v_dyn,1,v_retVal);     &nbs= p;      =20 =
v_back:=3Ddbms_sql.EXECUTE(v_dyn);      &nbsp= ;     =20
 =
loop           &nbsp= ;     =20
 exit when dbms_SQL.FETCH_ROWS=20
 =
(v_dyn)=3D0;          &nb=
sp;      =20 dbms_sql.COLUMN_VALUE(v_dyn,1,=20
 =
v_retVal);          &nbsp= ;      =20 =
dbms_output.put_line(to_char(v_retVal));     &nbsp= ;      =20 end loop;      end loop end; This = works fine=20
 on AUTONOMOUS_TRANSACTIONS. My question is: can I execute = the=20
 function on a other way like select pkg.fk from dual = ?? Wolfgang=20
 Ludewig -- Please see the official ORACLE-L FAQ: <A=20 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> -- = Author:=20
 Gabriel Galanternik   INET: <A=20 =
href=3D"mailto:ggalanterni_at_tesis-oys.com.ar">ggalanterni_at_tesis-oys.com.ar= </A> Fat=20
 City Network Services    -- (858) 538-5051  FAX: =
(858)=20

 538-5051 San Diego, =
California        --=20 Public Internet access / Mailing=20
 =

Lists<BR>----------------------------------------------------------------=

---- To=20
 REMOVE yourself from this mailing list, send an E-Mail message to: = <A=20
 href=3D"mailto:ListGuru_at_fatcity.com">ListGuru_at_fatcity.com</A> (note = EXACT=20
 spelling of 'ListGuru') and in the message BODY, include a line = containing:=20
 UNSUB ORACLE-L (or the name of mailing list you want to be removed=20 from).  You may also send the HELP command for other = information (like=20
 subscribing). -- Please see the official ORACLE-L FAQ: <A=20 href=3D"http://www.orafaq.com">http://www.orafaq.com</A> -- = Author:=20
 MacGregor, Ian A.   INET: <A=20 =
href=3D"mailto:ian_at_SLAC.Stanford.EDU">ian_at_SLAC.Stanford.EDU</A> Fa= t City=20
 Network Services    -- (858) 538-5051  FAX: (858)=20 538-5051 San Diego, =
California        --=20 Public Internet access / Mailing=20
 =

Lists<BR>----------------------------------------------------------------=