Home » SQL & PL/SQL » SQL & PL/SQL » PRAGMA AUTONOMOUS_TRANSACTION with DBLINK
PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461107] Wed, 16 June 2010 10:19 Go to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Hi guys,
I have a function declared as PRAGMA AUTONOMOUS_TRANSACTION.
If i execute this function everything is fine.
If I call this function from a remote database, I have this error message:

"ORA-14551: cannot perform a DML operation inside a query".


select function('parameter') from dual;
Result: "OK"

select function@dblink1('parameter') from dual;
Result: "ORA-14551: cannot perform a DML operation inside a query"


Anyone can help me ?

Thanks
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461109 is a reply to message #461107] Wed, 16 June 2010 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-14551: cannot perform a DML operation inside a query
 *Cause:  DML operation like insert, update, delete or select-for-update
          cannot be performed inside a query or under a PDML slave.
 *Action: Ensure that the offending DML operation is not performed or
          use an autonomous transaction to perform the DML operation within
          the query or PDML slave.

Can you post more?
A full test case representing your problem and we can reproduce but basically you are trying to do something that is not allowed.

Regards
Michel
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461111 is a reply to message #461109] Wed, 16 June 2010 10:37 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

My Function:

create or replace function lock_id(id_input in varchar2) Return varchar2 is
  
  PRAGMA AUTONOMOUS_TRANSACTION;  
  
  res varchar2(32000);

  begin

      update table_id
         set status = '10000'
       where id = id_input;
    
       
     res := '0: Ok';
    
    commit;
    
    return(res);
    
  exception
      
    when others then
      res  := '-2: Error: ' || TO_CHAR(SQLCODE) || ':' || SQLERRM;

      return(res);
         
  end lock_id;  



Call on local DB1 (9i):

select lock_id('9.42.33232') from dual

res = OK


Call from a remote DB2 (10g):

select lock_id@dblink_DB1('9.42.33232') from dual

res = ORA-14551



[Updated on: Wed, 16 June 2010 10:38]

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461113 is a reply to message #461111] Wed, 16 June 2010 10:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It may be a stupid question, but why are you calling this function in a SELECT statment.
Why not just call it directly from plsql?
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461114 is a reply to message #461113] Wed, 16 June 2010 11:06 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

My colleague needs to call this function from JBoss Application Server that work on another DB. I wrote this function as a procedure, but he needs a function.
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461115 is a reply to message #461114] Wed, 16 June 2010 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell us to write a correct application.
Database is not there to workaround bad skilled developer.

By the way, an autonomous code must ALWAYS end by a commit or a rollback.
And you MUST NOT use WHEN OTHERS to gobble the exception, exception is a normal return, application developer should be able to handle it.

Regards
Michel
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461121 is a reply to message #461114] Wed, 16 June 2010 12:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Even if it's a function, you don't need to call it from SQL.

Call it like this:
DECLARE
  v_ret   varchar2(32767);
BEGIN
  v_ret := lock_id@dblink_DB1('9.42.33232');


  <other code to actually do something>
END;
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461129 is a reply to message #461121] Wed, 16 June 2010 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60016
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some bl... bad software only allows SELECT.
I have only one advice about them: throw away.

Regards
Michel
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461145 is a reply to message #461115] Wed, 16 June 2010 13:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
Senior Member
Bug 6763226 - ORA-14551 WHILE CALLING FUNCTION IN SQL OVER DBLINK [ID 877075.1]. Fixed in 11.2 https://support.oracle.com:443/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(from=BOOKMARK&bmDocD src=KB&bmDocType=PROBLEM&bmDocID=877075.1&viewingMode=1143&bmDocTitle=ORA-14551%20WHILE%20CALLING%20FUNCTION%20IN%20S QL%20OVER%20DBLINK))

SY.
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461321 is a reply to message #461145] Thu, 17 June 2010 06:59 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Thank you for your support

[Updated on: Thu, 17 June 2010 06:59]

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461327 is a reply to message #461111] Thu, 17 June 2010 07:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I can not believe the output you provided.
select lock_id('9.42.33232') from dual

res = OK


Is this exact copy paste of your SQL session?

regards,
Delna
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461392 is a reply to message #461327] Thu, 17 June 2010 11:44 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
I have comments:

1) what are the versions of databases you are calling. As I remember it, this error is an older oracle error that was common when AUTONOMOUS TRANSACTIONS were first introduced. It may be that one of the databases you are executing on is an older oracle release (8i maybe?).

2) it may be you are not executing the code you think you are. Do you have a copy of the function that does not contain the autonomous transaction pragma? You may be calling that one by accident.

3) there are several errors in the function
a) your return variable is defined as 32000 but sql can only handle a maximum of 4000 (assuming you are on a newer release otherwise 2000).
b) there is no commit in your exception handler. You should issue a commit or rollback in the exception handler (I suggest rollback).

4) there is a fundamental flaw in having functions update data from a query. oracle does not ever consider that your query return items may be updating data. Oracle is thus free to optimize execution of every query however it sees fit. Many optimizations include optimizing the execution of functions called in a query. This leads to a simple question: given your select statement:

select lock_id('9.42.33232') from dual

How many times will Oracle call this function?

One might assume that since there is one row in DUAL, that the row will be fetched once and thus oracle will call the function once. However if Oracle is optimizing this query, it can decide to

a) return one row and call the function once (maybe)
b) restart the query for some reason and thus for one logical invocation call the function more than once
c) remember that it called this function before and for whatever reason reuse the value from that call and thus not call the function at all

You case is pretty simple so it is a reasonable bet that you will call the function once each time and only once each time, but still it is not a sure thing, and for sure as a general practice if you get more complex sql you cannot rely on a one-to-one function execution per row returned. There are several situations I can point to:

1) use of views where the column is not selected
2) column referenced but not necessary to the query because of nesting
3) oracle rewrite that removes the column for some other reason
4) code that gets restarted (thing trigger restart)
5) functions used in the WHERE clause/HAVING clause/GROUP BY clause etc.
6) likely many other situations I don't know about

Tom Kyte of asktomhome has a discussion of this if you can find it.

5) the name of your function suggest one of your goals is to lock the associated row. If so you have failed. Since there must be a commit/rollback in your AUTONOMOUS TRANSACTION, the lock will be "released" when you commit/rollback.

Under your circumstances I am not sure what else you can do but go with it. Maybe someone else has an idea?

Gooc luck. Kevin

[Updated on: Thu, 17 June 2010 11:53]

Report message to a moderator

Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461458 is a reply to message #461392] Fri, 18 June 2010 02:40 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Thank you for you answer... Wink
really useful
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #461473 is a reply to message #461458] Fri, 18 June 2010 03:58 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
I wish I had something better for you but I fear there is no easy solution. Maybe you can consider the old saying:

Patient:  "DOCTOR DOCTOR IT HURTS WHEN I DO THIS..."

Doctor:  "SO DON'T DO THAT"

Kevin
Re: PRAGMA AUTONOMOUS_TRANSACTION with DBLINK [message #564316 is a reply to message #461473] Wed, 22 August 2012 09:57 Go to previous message
dominikl
Messages: 1
Registered: August 2012
Location: Slovenia
Junior Member
Workaround is to use wrapper for function that is called over dblink.
SELECT WRAPPED_FUN@DB_LINK FROM DUAL

And this function must execute dynamically function that is declared with AUTONOMOUS pragma.
vi EXECUTE IMMEDIATE 'SELECT MY_AUTON_FUN ... FROM DUAL' INTO LI_RES.

Note: metalink does not come up with this workaround.
Previous Topic: PL/SQL Help
Next Topic: trunc(sysdate-1) or trunc(sysdate)-1
Goto Forum:
  


Current Time: Mon Dec 22 04:55:56 CST 2014

Total time taken to generate the page: 0.05072 seconds