5 Replies Latest reply on May 18, 2018 1:45 PM by keith Yim-Lim

    Issue with Parameterized Statements for Oracle

    keith Yim-Lim

      Hi,

       

      I have a procedure in Oracle

       

      create or replace PROCEDURE procOrchestrator(paramIN IN VARCHAR,paramOUT OUT VARCHAR)

      IS

      BEGIN

       

       

        DBMS_OUTPUT.PUT_LINE('TestOrchestration' || paramIN);

        paramOUT := ('TEST MESSAGE' || paramIN);

       

       

      END;

       

      I'm using the parameterized statements to execute this procedure but getting an error message.

      Statements input XML

      <statements>

      <statement>

        <procedure>

         <name>procOrchestrator</name>

         <argument-set>

          <argument>

           <in-out>IN</in-out>

            <data-type>VARCHAR</data-type>

            <value>test</value>

          </argument>

          <argument>

      <in-out>OUT</in-out>

      <data-type>VARCHAR</data-type>

          </argument> 

         </argument-set>

        </procedure>

      </statement>

      </statements>

       

       

      [adapter response=

      <sql-adapter-response>

        <statement-responses>

          <statement-response>

            <statement-metadata>

              <error>Prepared Statement Query not specified</error>

              <status>error</status>

            </statement-metadata>

          </statement-response>

        </statement-responses>

      </sql-adapter-response>

       

      If anyone can help.

       

      Thanks

        • 1. Re: Issue with Parameterized Statements for Oracle
          Ranganath Samudrala

          Please attach complete grid.log file to identify where the error is coming from.

          • 2. Re: Issue with Parameterized Statements for Oracle
            keith Yim-Lim

            16 May 2018 15:02:42,627 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  Adapter Request :: <adapter-request>

              <target-adapter>AdapterConfiguration1526329163491--1426771575</target-adapter>

              <peer-location>

                <location>any</location>

              </peer-location>

              <request-action />

              <ttl>4611686018427387871</ttl>

              <job-id>119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579</job-id>

              <request-data>

                <sqlAdapterRequest>

                  <targets />

                  <statements>

                    <statement>

                      <procedure>

                        <name>procOrchestrator</name>

                        <argument-set>

                          <argument>

                            <in-out>IN</in-out>

                            <data-type>VARCHAR</data-type>

                            <value>test</value>

                          </argument>

                          <argument>

                            <in-out>OUT</in-out>

                            <data-type>VARCHAR</data-type>

                          </argument>

                        </argument-set>

                      </procedure>

                    </statement>

                  </statements>

                </sqlAdapterRequest>

              </request-data>

            </adapter-request>

            16 May 2018 15:02:42,628 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: ADAPTER ACTION: null

            16 May 2018 15:02:42,628 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: REQUEST JOBID: 119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579

            16 May 2018 15:02:42,628 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: REQUEST: <sqlAdapterRequest>

              <targets />

              <statements>

                <statement>

                  <procedure>

                    <name>procOrchestrator</name>

                    <argument-set>

                      <argument>

                        <in-out>IN</in-out>

                        <data-type>VARCHAR</data-type>

                        <value>test</value>

                      </argument>

                      <argument>

                        <in-out>OUT</in-out>

                        <data-type>VARCHAR</data-type>

                      </argument>

                    </argument-set>

                  </procedure>

                </statement>

              </statements>

            </sqlAdapterRequest>

            16 May 2018 15:02:42,628 [Thread=AMP - Perform Action Executor - 15] INFO  SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  Request validation started

            16 May 2018 15:02:42,628 [Thread=AMP - Perform Action Executor - 15] INFO  SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  Request validation completed

            16 May 2018 15:02:42,628 [Thread=AMP - Perform Action Executor - 15] DEBUG StatementWorker [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [JobId: 119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579][Thread: AMP - Perform Action Executor - 15] [adapter: OracleAdapterTest]invokeMultiplePreparedStatements() invokePreparedStatement: invoking prepared statements on db [VENDOR=Oracle][VERSION=Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production]

            16 May 2018 15:02:42,629 [Thread=AMP - Perform Action Executor - 15] DEBUG StatementWorker [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [JobId: 119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579][Thread: AMP - Perform Action Executor - 15] [adapter: OracleAdapterTest]invokePreparedStatement(XML, action) getting database connection

            16 May 2018 15:02:42,629 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: SqlActorAdapter.getDatabaseConnection() enter

            16 May 2018 15:02:42,629 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: Get database connection for url: jdbc:oracle:thin:@(DESCRIPTION =(ENABLE=BROKEN)(ADDRESS = (PROTOCOL = TCP)(HOST = saorabid1)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCHESTRAD)))

            16 May 2018 15:02:42,734 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: Database BasicDataSource datasource statistics [NumActive:1][NumIdle: 0]

            16 May 2018 15:02:42,734 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: SqlActorAdapter.getDatabaseConnection() leave

            16 May 2018 15:02:42,734 [Thread=AMP - Perform Action Executor - 15] DEBUG StatementWorker [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [JobId: 119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579][Thread: AMP - Perform Action Executor - 15] [adapter: OracleAdapterTest]invokePreparedStatement(XML, action) Prepared Statement request

            <statement>

              <procedure>

                <name>procOrchestrator</name>

                <argument-set>

                  <argument>

                    <in-out>IN</in-out>

                    <data-type>VARCHAR</data-type>

                    <value>test</value>

                  </argument>

                  <argument>

                    <in-out>OUT</in-out>

                    <data-type>VARCHAR</data-type>

                  </argument>

                </argument-set>

              </procedure>

            </statement>

            16 May 2018 15:02:42,735 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: SqlActorAdpater:close() enter

            16 May 2018 15:02:42,735 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: SqlActorAdpater:close() closing result set, pStmt and connection

            16 May 2018 15:02:42,735 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: Basic DataSource : Closing connection: jdbc:oracle:thin:@(DESCRIPTION =(ENABLE=BROKEN)(ADDRESS = (PROTOCOL = TCP)(HOST = saorabid1)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCHESTRAD))), UserName=ORCHESTRATOR, Oracle JDBC driver

            16 May 2018 15:02:42,735 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: Basic DataSource : Before closing connection : Basic DataSource Statistics: [NumActive= 1][NumIdle= 0]

            16 May 2018 15:02:42,735 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: Basic DataSource : After closing connection : Basic DataSource Statistics: [NumActive= 0][NumIdle= 1]

            16 May 2018 15:02:42,735 [Thread=AMP - Perform Action Executor - 15] DEBUG SqlActorAdapter [PeerName=CDP] [JobID=119cc91f7b5cfc66:-646fbd65:1635ffa1d7b:-80001-1526497362579] [AdapterName=OracleAdapterTest]  [Thread: AMP - Perform Action Executor - 15 ] OracleAdapterTest: SQLAdapter ResponseXML :

            <sql-adapter-response>

              <statement-responses>

                <statement-response>

                  <statement-metadata>

                    <error>Prepared Statement Query not specified</error>

                    <status>error</status>

                  </statement-metadata>

                </statement-response>

              </statement-responses>

            </sql-adapter-response>

            • 3. Re: Issue with Parameterized Statements for Oracle
              Matthew Highcove

              This request does not match the examples in the documentation. In the examples, you can either use a <statements> element or a <procedure> element but not both. If you use <statements>, you must also use a <query> with the SQL query you want to run. The adapter sees <statements>, expects a <query>, does not find one, and returns an error to say the query was not specified. If you want to use a stored procedure, omit the <statements>:

              <sqlAdapterRequest>

                <procedure>

                  <name>test_Out_SP</name>

                  <argument-set>

                    <argument>

                      <in-out>IN</in-out>

                      <data-type>VARCHAR</data-type>

                      <value>test</value>

                    </argument>

                    <argument>

                      <in-out>OUT</in-out>

                      <data-type>VARCHAR</data-type>

                    </argument>

                  </argument-set>

                </procedure>

              </sqlAdapterRequest>

              • 4. Re: Issue with Parameterized Statements for Oracle
                keith Yim-Lim

                Thanks Mathew

                I was able to get it work.

                 

                Calling a stored procedure via the Atrium Orchestrator SQL adapter fails with "Action must be query or update in request"

                On top of what you said we also need to follow the procedure above to specify the action when calling the adapter. If not that error will be generated

                <error>error occured parsing request: Message[summary=An exception occured in adapter MySQLAdapter. Action must be query or update in request, detail=An exception occured in adapter MySQLAdapter. Action must be query or update in request]</error>

                 

                +

                 

                Add the index tag

                 

                <procedure>

                <name>procOrchestrator</name>

                <argument-set>

                  <argument>

                   <index>1</index>

                   <in-out>IN</in-out>

                   <data-type>VARCHAR</data-type>

                   <value>test</value>

                  </argument>

                  <argument>

                   <index>2</index>

                   <in-out>OUT</in-out>

                   <data-type>VARCHAR</data-type>

                  </argument> 

                </argument-set>

                </procedure>

                • 5. Re: Issue with Parameterized Statements for Oracle
                  keith Yim-Lim

                  On another note:

                  I have an oracle function

                  create or replace FUNCTION funcOrchestrator (p_result OUT VARCHAR2,p_message OUT VARCHAR2)

                    RETURN VARCHAR

                  IS

                   

                  BEGIN

                     p_result  := ('Success');

                     p_message := ('');

                     RETURN 'True';

                  EXCEPTION

                    WHEN OTHERS THEN

                         p_result  := ('Failure');

                     p_message := ('Failure message:');  

                     RETURN 'False';

                  END;

                   

                  How would i build my statement in parameterized statement to get the output + the return in the adapter response

                  I've been trying several statements but most of the time..invalid index.

                   

                  <statements>

                  <statement>

                  <query>select funcOrchestrator (?,?) from dual</query>

                  <parameter-sets>

                  <parameter-set>

                  <parameter>

                  <index>1</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  </parameter>

                  <parameter>

                  <index>2</index>

                  <in-out>IN</in-out>

                  <data-type>VARCHAR</data-type>

                  </parameter>

                  <parameter>

                  <index>3</index>

                  <in-out>OUT</in-out>

                  <data-type>VARCHAR</data-type>

                  </parameter>

                  <parameter>

                  <index>4</index>

                  <in-out>OUT</in-out>

                  <data-type>VARCHAR</data-type>

                  </parameter>

                  <parameter>

                  <index>5</index>

                  <in-out>OUT</in-out>

                  <data-type>VARCHAR</data-type>

                  </parameter>

                  </parameter-set>

                  </parameter-sets>

                  </statement>

                  </statements>

                   

                  Any ideas?

                   

                  Thanks