Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DML from a function

Re: DML from a function

From: Randy Harris <randy_at_SpamFree.com>
Date: Mon, 28 Mar 2005 15:12:04 GMT
Message-ID: <89V1e.26157$hU7.11306@newssvr33.news.prodigy.com>


"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1111990007.733529_at_yasure...
> Randy Harris wrote:
>
> > "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> > news:1111948844.640991_at_yasure...
> >
> >>Randy Harris wrote:
> >>
> >>>I've written a function to handle the Interval for a DBMS_JOB (with
help
> >>>from folks here). I wanted to have it write to a log each time it ran,
> >
> > so I
> >
> >>>added a simple INSERT into it. I promptly got an error that DML
> >
> > statement
> >
> >>>are not permitted in a "query" (I guess it means a function?). I
> >
> > figured,
> >
> >>>no problem, I'll simply create a Procedure that will write to the log
> >
> > and
> >
> >>>call the procedure from the function. That caused the same error. Is
> >
> > there
> >
> >>>a "simple" way around this?
> >>
> >>It would be helpful to know what error message you received, too see the
> >>DML, etc. but given what little you have provided ... look at using
> >>PRAGMA AUTONOMOUS TRANSACTION.
> >>
> >>www.psoug.org
> >>click on Morgan's Library
> >>click on Autonomous Transaction
> >>for an example
> >>--
> >>Daniel A. Morgan
> >>University of Washington
> >>damorgan_at_x.washington.edu
> >>(replace 'x' with 'u' to respond)
> >
> >
> >
> > This is the error message:
> >
> > ORA-14551: cannot perform a DML operation inside a query
> >
> > This is the DML:
> >
> > INSERT INTO job_log VALUES ('Web Push', TRUNC(SYSDATE), next_date);
> > Oracle version is 8.1.7.4
> >
> > Platform is HP-UX 11.0
>
> I can't see anything about this insert statement that would trigger
> the error provided next_date is a variable. Are you sure this is the
> source of the error message?
>
> One comment though ... what you wrote is very bad SQL. You should
> list the columns into which the values are being inserted.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

This is what I am getting with the INSERT in the function:

SQL> begin dbms_output.put_line(nextwebpush); end;   2 /

PL/SQL procedure successfully completed.

SQL> select nextwebpush from dual;
select nextwebpush from dual

       *
ERROR at line 1:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "LMS.NEXTWEBPUSH", line 25
ORA-06512: at line 1

SQL> Thomas Kyte explained in another thread, that the "interval" was selected this way in the DBMS_JOB process. I've got the function choosing the correct days and times now. I had added the INSERT to log for debugging when I ran into this. I changed "INSERT into a log table" to a "write to a log file". That is working. Based on what I'm seeing in that file, it looks as though the "interval" function is run twice every time the job is run. I think it's once just before the job is run, then again after.

-- 
Randy Harris
(tech at promail dot com)
Received on Mon Mar 28 2005 - 09:12:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US