2

I am writing a PL/SQL procedure (Oracle) that will be called by a J2EE program. This J2EE program has started a distributed transaction and my PL/SQL procedure will be part of this transaction. I wonder if it is allowed to use a dblink inside my procedure. I guess it may cause some problems because the use of a dblink implies a distributed transaction managed by Oracle, but there is already a distributed transaction managed by the J2EE container. Is it forbidden ? or allowed with some restriction ?

Thank you for your answers.

jmb
  • 129
  • 11
  • 2
    Have you tried it to see what happens? – Alex Poole Jun 18 '14 at 10:09
  • Some years ago, I was using weblogic 8.1, and I got some weird errors which dissapeared when I rewrite some PL/SQL code with dblink in java. I supposed that it was forbidden, but perhaps it is allowed with some restriction. Today, I use Tomee and PL/SQL packages which are not my own. I get no error, but the program does not work as it should. I don't know yet if this is because of program bugs, tomee bugs or because of this dblink. – jmb Jun 20 '14 at 09:44
  • I could believe a bug if the DB link was actually in the JDBC statement maybe. But if it's only inside a procedure you're calling I don't see how that would behave differently when called over JDBC vs. from SQL*Plus or some other client. A distributed transaction might complicate this though. Are you performing DML over the DB link, or just querying? Are you querying data modified by the other part of the distributed transaction? – Alex Poole Jun 20 '14 at 10:55

1 Answers1

1

If you are talking about XA Distributed Transactions then such configuration is supported, but with some limits (It depends on the version of your Oracle DB). For more details you can check this document Pay attention to the section "XA and Database Links"

neshkeev
  • 6,280
  • 3
  • 26
  • 47
  • Thank you for your answer. This paper is exactly what I was looking for. I start ma reading just now. – jmb Jun 30 '14 at 14:15