Home » SQL & PL/SQL » SQL & PL/SQL » DML Statement in Function
DML Statement in Function [message #318545] Wed, 07 May 2008 02:45 Go to next message
hasnainlakhani
Messages: 24
Registered: January 2007
Junior Member
I use DML Statement in Function . When I execute the function it gives me error that "Cannot Perform DML Operation Inside a Query"

How can I resolve this problem. Is there any way to use DML statement using Function (Not Procedure)


Re: DML Statement in Function [message #318548 is a reply to message #318545] Wed, 07 May 2008 02:51 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You CAN use DML in a function, but then you can only use the function in PL/SQL, NOT directly in an SQL Query.

e.g.

....
v_val := function(parameter);
....


in PL/SQL will work, using

select function(parameter) 
  from .......

directly in SQL will not work.

So there is no workaround for the "Cannot Perform DML Operation Inside a Query"

[Updated on: Wed, 07 May 2008 02:51]

Report message to a moderator

Re: DML Statement in Function [message #318551 is a reply to message #318548] Wed, 07 May 2008 03:03 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

SQL> create table x (col1 char(1));

Table created.

SQL> create or replace function myfunc return number
  2  is
  3  PRAGMA AUTONOMOUS_TRANSACTION;
  4  begin
  5  insert into x (col1) values('A');
  6  commit;
  7  return 1;
  8  end;
  9  /

Function created.

SQL> select myfunc from dual;

    MYFUNC
----------
         1

SQL> select * from x;

C
-
A

Re: DML Statement in Function [message #318578 is a reply to message #318551] Wed, 07 May 2008 03:40 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Don't like that advice..
Considering the fact that the original poster did not understand why he could not do DML in a function called from SQL, we would not want him to go on the dangerous path of autonomous transactions.
Re: DML Statement in Function [message #318589 is a reply to message #318578] Wed, 07 May 2008 04:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Point taken
Re: DML Statement in Function [message #319022 is a reply to message #318545] Thu, 08 May 2008 14:05 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
try this:

Kevin Meade's OraFAQ Blog

Autonomous Transactions: a Poor Mis-Understood Feature

Tells you most of the things you shouldn't do with an Autonomous Transaction and why.

Kevin
Previous Topic: delete multiple tables
Next Topic: Option to keep running a PL/SQL package automatically
Goto Forum:
  


Current Time: Tue Feb 11 16:53:45 CST 2025