7 Replies Latest reply: Jul 3, 2013 6:18 AM by Marcel Mertin RSS

    DB Adapter Polling with muliple ORDER BY Columns

    Marcel Mertin

      Hi,

       

      we are using Oracle SOA Suite 11g with AIA. Currently we are using in one scenario the DB Adapter to poll one DB table with a ORDER BY statement on a single column (DATECREATED). Now we would like to extend the ORDER BY with priority. In DB Adapter Wizard it is not possible to edit the pure SQL. Is it possible to edit the query somewhere else? Is the DB Adapter Polling restricted to have only one ORDER BY column?

       

      Bye
      Marcel

        • 1. Re: DB Adapter Polling with muliple ORDER BY Columns
          Karan Mann

          You can have multiple OrderBy columns.Just in the wizard where you select the order by column,manually type the name of other columns using comma(,) as separator in the orderby text box.

           

           

          Hope this helps.

           

          Regards,

          Karan

          http://learn-oraclesoa.blogspot.com/

          • 2. Re: DB Adapter Polling with muliple ORDER BY Columns
            Marcel Mertin

            Hi Karan,

             

            thank you, it is a good hint but seems to be not working. We try it with comma(,) and space before and generated SQL set OrderBy columns in quotes("). Without space first it seems to work, but after deployment and startup of composite I got the error: Caused by: java.sql.SQLSyntaxErrorException: ORA-00960: ambiguous column naming in select list

            I check the generated SQL and see that the "new" OrderBy column attached in SELECT statement. See the screenshot in my Dropbox https://www.dropbox.com/s/n99pimlflqeh2gm/DBAdapterWizardOrderBy.png

             

            Why it is not possible to edit the SQL in a Polling Function? The field is disabled. See the screenshot here: https://www.dropbox.com/s/obkfk8iqg855qtv/DBAdapterWizardSQLNoEdit.png In a "normal" SELECT I'm able to edit the pre-generated SQL.

             

            Greetings

            Marcel

            • 3. Re: DB Adapter Polling with muliple ORDER BY Columns
              Karan Mann

              Are you not able to Edit the generated SQL by clicking on the Edit button in the last screenshot you uploaded.I can edit it and also order by clause is working for me with 2 columns.

               

              Can you please retry once.

               

              Regards,

              Karan

              http://learn-oraclesoa.blogspot.com/

              • 4. Re: DB Adapter Polling with muliple ORDER BY Columns
                Marcel Mertin

                Hi,

                 

                I'm not able in case of Polling only in a simple SELECT. Do you also try a polling adapter? We are using 11.1.5.0 - I will try it with the latest version.

                 

                Bye

                Marcel

                • 5. Re: DB Adapter Polling with muliple ORDER BY Columns
                  Marcel Mertin

                  Hello,

                   

                  also with the latest Version 11.1.1.7.0 I'm not able to edit the SQL statement when I choose "Poll for New or Changed Records in a Table". How could I fix this? Is there a workaround e.g. modify the generated JCA File.

                   

                  Regards,

                  Marcel

                  • 6. Re: DB Adapter Polling with muliple ORDER BY Columns
                    user11274260

                    Try by adding the column separated by comma in the jca file property SequencingColumn

                    • 7. Re: DB Adapter Polling with muliple ORDER BY Columns
                      Marcel Mertin

                      Hi,

                       

                      I have already test it, but the same behavior as if I using the Wizard. May be the Exception will help:

                      <BEA-000000> <Database Adapter TriggerServiceRequestFilenetDBAdapterV1 <oracle.tip.adapter.db.InboundWork handleException> Encountered a fatal exception while polling.  Will continue polling but with minimal logging.  Please investigate the fault and manually stop polling from the console if in development and this appears to be a modeling mistake.  BINDING.JCA-11624

                      DBActivationSpec Polling Exception.

                      Query name: [ECMActivityTriggerDBAdapterSelect], Descriptor name: [ECMActivityTriggerDBAdapter.EcmInterface]. Polling the database for events failed on this iteration.

                      Caused by java.sql.SQLSyntaxErrorException: ORA-00960: ambiguous column naming in select list

                       

                      In case of a simple SELECT Adapter the gernerate Query in *or-mappings.xml looks different. In case of SELECT there is the SQL statement in "plain" text:

                           <querying>
                                  <queries>
                                     <query name="SelectTESTSelect" xsi:type="read-all-query">
                                        <call xsi:type="sql-call">
                                           <sql>SELECT EVTKEY, ROW_CREATED, CUST_SOURCECHANNEL FROM ECM_INTERFACE ORDER BY ROW_CREATED, CUST_SOURCECHANNEL</sql>
                                        </call>
                                        <reference-class>SelectTEST.EcmInterface</reference-class>
                                        <container xsi:type="list-container-policy">
                                           <collection-type>java.util.Vector</collection-type>
                                        </container>
                                     </query>
                                  </queries>
                               </querying>
                      

                       

                      In case of TRIGGER there is no SQL:

                       

                      <querying>
                                  <queries>
                                     <query name="ECMActivityTriggerDBAdapterSelect" xsi:type="read-all-query">
                                        <reference-class>ECMActivityTriggerDBAdapter.EcmInterface</reference-class>
                                        <lock-mode>none</lock-mode>
                                        <container xsi:type="list-container-policy">
                                           <collection-type>java.util.Vector</collection-type>
                                        </container>
                                     </query>
                                  </queries>
                               </querying>
                      

                       

                      It seems the SOA Suite will generate the SQL during runtime.

                       

                      Best regards,
                      Marcel