Re: DML from a function

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sun, 27 Mar 2005 22:10:21 -0800
Message-ID: <1111990007.733529@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)

Received on Mon Mar 28 2005 - 00:10:21 CST