2 Replies Latest reply on Jan 31, 2006 2:14 PM by Devious Scully

    DatabaseOperation transformation

    Devious Scully
      OracleAS 10g Release 2 (10.1.2)

      I have a DatabaseOperation transformation which calls a PL/SQL function in a database that is neither the source, target or hub database. The adapter is FTP for testing purposes.

      The iStudio transformation reads:

      DatabaseOperation (aniteCreateMemo.serviceMemoInputParameters.aniteFolder1Reference, "****", "****", "****host****", "****", "****", "thin", "1", "10", "begin ?o:s=fgv_get_curr_pcode(?i:s); end;")

      I can tnsping and connect to the database using SQL*Plus and TOAD from my PC and the hub server. However, the adapter logs indicates an error for which I can't fathom a solution

      And my error message reads:

      oracle.oai.agent.server.transform.database.DBTransformationException: PLSQLTransformation.transform: Couldn't get JDBC connection.
      at oracle.oai.agent.server.transform.database.PLSQLTransformation.transform(PLSQLTransformation.java:277)
      at oracle.oai.agent.server.transform.BuiltInTransformation.transform(BuiltInTransformation.java:298)
      at oracle.oai.agent.server.transform.MessageTransformer.processStackFrame(MessageTransformer.java:846)
      at oracle.oai.agent.server.transform.MessageTransformer.processTransformMetadatalet(MessageTransformer.java:487)
      at oracle.oai.agent.server.transform.MessageTransformer.transformMessage(MessageTransformer.java:274)
      at oracle.oai.agent.server.InMessageTransformer.processObject(InMessageTransformer.java:87)
      at oracle.oai.agent.common.QueueAgentComponent.run(QueueAgentComponent.java:110)
      at java.lang.Thread.run(Thread.java:534)
      Mon Jan 30 11:44:18 GMT 2006: Aborting transformMessage().

      Ultimately, the message is dropped.

      Has anyone seen this problem before?

      Message was edited by:
      Devious Scully

      Message was edited by:
      Devious Scully
        • 1. Re: DatabaseOperation transformation
          Devious (love the name btw),
          I've never tried using the DatabaseOperation transformation from any other adapter type than a Database Adapter, so I don't know if this is causing the problem. I'm unsure as to whether the Bridge code of the adapter is used to perform this type of transform. However, if so, the ftp bridge does not know how to connect via JDBC or SQLNet. I may be barking up the wrong tree on this however, but it would seem reasonable?!?.

          Two things I'd try.
          1. Create another DB adapter / use an existing DB adapter and try the same transform on that. This should prove or disprove the above theory.
          2. Increase your "retry count". I think it is set to "1". Increase it to 5, and have a "retry delay" of "5".

          • 2. Re: DatabaseOperation transformation
            Devious Scully

            It's a good theory but I don't believe that's the problem. There are a couple of things I have tried which may help.

            Firstly, I have retained the transformation in the ftp adapter but changed the connection data to point at another Oracle database. This time I am able to get a JDBC connection. Incidentally, the hub, adapter and target database are all now hosted on the same server whereas they weren't before (the target was a different server). I believe both databases are at the same version (need to verify) but it could be that JDBC connections are prohibited by the target database. If you have any thoughts on that I'm listening.

            Secondly, if you take a look at an FTP adapter start script the classpath contains the same jar files as that of the db adapter, plus additional ones. That says to me that if the db adapter can do it, the ftp can too. Plus, I haven't seen any text indicating a restriction on the use of this transformation.

            In summary, all this appears as though we won't take advantage of the transformation and I'll end up using an alternative, but I still have your suggestions to try offline.