Re: DML from a function

Randy Harris (randy_at_SpamFree.com) 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?).

No, as a previous poster mentioned, to get the result of your function oracle (apparently) does a "SELECT your_function() FROM dual;"

That's a query, and normally, a function called from a query is not allowed to have side effects.

I googled on " oracle query function side effect " and the second hit had some useful data about this. A better google query would turn up more.

They mention autonomous transaction (hope I quoted that right), which might help. That allosw you to have a procedure which you can call that can commit without commiting the callers transaction - glancing very briefly, it sounds like such a procedure might be useable indirectly within a query.

--

This space not for rent.