6 Replies Latest reply: Mar 25, 2013 7:07 AM by Stuart Fleming RSS

    DBadapter on the Hr.employees table

    Stuart Fleming
      Hello,
      I am trying to create a dbadapter service that will grab a single record from the hr.employees table that ships with Oracle 11g.

      I have been successful building a one-record dbadapter service on the departments table, but the employee table has a unique foreign key (to itself on the manager's field).

      The dpadapter is looking only at the employees table, but on the "Attribute Filtering" screen of the wizard, it shows the "employeesCollection" as selected (and it is disabled, unfortunately).
      The query is:

      SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID FROM EMPLOYEES WHERE (EMPLOYEE_ID = #pEmpID)

      The results of the payload vary based on the data entered. If I run a query to look at king, EMPLOYEE_ID 100, then it shows everyone, if I look at EMPLOYEE_ID 204, it shows fewer. Seems to show the location on the "tree" of data that is created.

      I have tried various fudges with the xsd, etc to see if I could limit the data to one record, but with no success. I could make a view that does not show the foreign key, but would like to know if it can be done in SOA Suite using the tools.

      Thank you,
      Stuart
        • 1. Re: DBadapter on the Hr.employees table
          Abhinav
          Stuart,
          The results of the payload vary based on the data entered. If I run a query to look at king, EMPLOYEE_ID 100, then it shows everyone, if I look at EMPLOYEE_ID 204, >it shows fewer..
          For record 100 there is no Manager_Id and for 204 there is exists Manager_Id (101).
          The second employeesCollection shows respective Manager details.. as Employees has 1:M relationship with Employees(employeesCollection)

          1. The test is done using Select DB operation
          Request:
          <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
               <soap:Body>
                    <ns1:HR.EmployesSelect_empIDInputParameters xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/db/top/HR/Employes">
                         <ns1:empID>204</ns1:empID>
          </ns1:HR.EmployesSelect_empIDInputParameters>
          </soap:Body>
          </soap:Envelope>

          Response:
          <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
               <env:Header>
                    <wsa:MessageID>urn:1FE04C60943F11E29F04C9D4B199D4A5</wsa:MessageID>
                    <wsa:ReplyTo>
                         <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
                         <wsa:ReferenceParameters>
                              <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">11d1def534ea1be0:-4e33ae05:13d9aa4ca94:-8000-000000000000089b</instra:tracking.ecid>
                         </wsa:ReferenceParameters>
                    </wsa:ReplyTo>
                    <wsa:FaultTo>
                         <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
                         <wsa:ReferenceParameters>
                              <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">11d1def534ea1be0:-4e33ae05:13d9aa4ca94:-8000-000000000000089b</instra:tracking.ecid>
                         </wsa:ReferenceParameters>
                    </wsa:FaultTo>
               </env:Header>
               <env:Body>
                    <top:EmployeesCollection xmlns:top="http://xmlns.oracle.com/pcbpel/adapter/db/top/HR/Employes">
                         <top:Employees>
                              <top:employeeId>204</top:employeeId>
                              <top:firstName>Hermann</top:firstName>
                              <top:lastName>Baer</top:lastName>
                              *<top:managerId>*
                         *<top:employeeId>101</top:employeeId>*
                              *<top:firstName>Neena</top:firstName>*
                              *<top:lastName>Kochhar</top:lastName>*
                         *</top:managerId>*
                         </top:Employees>
                    </top:EmployeesCollection>
               </env:Body>
          </env:Envelope>

          2. The test is done using Executing Pure SQL  operation with SQL : SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID FROM EMPLOYEES WHERE (EMPLOYEE_ID = #pEmpID)

          Request:
          <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
               <soap:Body>
                    <ns1:hr2Input xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/db/hr2">
                         <ns1:pEmpID xmlns:ns2="http://www.w3.org/2001/XMLSchema-instance" ns2:nil="true">204</ns1:pEmpID>
          </ns1:hr2Input>
          </soap:Body>
          </soap:Envelope>

          Response:
          <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
               <env:Header>
                    <wsa:MessageID>urn:E1EC5AB0943F11E29F04C9D4B199D4A5</wsa:MessageID>
                    <wsa:ReplyTo>
                         <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
                         <wsa:ReferenceParameters>
                              <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">11d1def534ea1be0:-4e33ae05:13d9aa4ca94:-8000-0000000000000a66</instra:tracking.ecid>
                         </wsa:ReferenceParameters>
                    </wsa:ReplyTo>
                    <wsa:FaultTo>
                         <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
                         <wsa:ReferenceParameters>
                              <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">11d1def534ea1be0:-4e33ae05:13d9aa4ca94:-8000-0000000000000a66</instra:tracking.ecid>
                         </wsa:ReferenceParameters>
                    </wsa:FaultTo>
               </env:Header>
               <env:Body>
                    <db:hr2OutputCollection xmlns:db="http://xmlns.oracle.com/pcbpel/adapter/db/hr2">
                         <db:hr2Output>
                              <db:EMPLOYEE_ID>204</db:EMPLOYEE_ID>
                              <db:FIRST_NAME>Hermann</db:FIRST_NAME>
                              <db:LAST_NAME>Baer</db:LAST_NAME>
                              *<db:MANAGER_ID>101</db:MANAGER_ID>*
                         </db:hr2Output>
                    </db:hr2OutputCollection>
               </env:Body>
          </env:Envelope>

          Hope it helps !!

          Regards,
          Abhinav
          • 2. Re: DBadapter on the Hr.employees table
            Stuart Fleming
            Abhinav,
            Yes, that was helpful. I have some questions, if you would be sure to answer #1, I would appreciate it.

            1. You specified that the first operation was "Select DB operation" while the second was "Executing Pure SQL operation"
            How does one specify one or the other. Is it by using the wizard vs writing your own sql in the dbadapter wizard? I don't see where to "set" this.

            2. For the most part the dbapdapter wizard is re-entrant, meaning you can double-click it to open it, run through it, and then see all of the previous selections made.

            HOWEVER, the "Relationships" screen is not -- If I make a selection once, and then "finish" the wizard, then the next time I go back in, nothing is selected. Is this by design, a bug or limited to the version I am running?

            Stuart
            • 3. Re: DBadapter on the Hr.employees table
              Abhinav
              Stuart,
              I would be happy to solve your query.
              How does one specify one or the other. Is it by using the wizard vs writing your own sql in the dbadapter wizard? I don't see where to "set" this.
              We can select the operation using DB adapter configuration wizard in Jdeveloper SOA Composite Designer window.
              http://docs.oracle.com/cd/E15523_01/integration.1111/e10231/adptr_db.htm#BDCGADFJ
              If I make a selection once, and then "finish" the wizard, then the next time I go back in, nothing is selected. Is this by design, a bug or limited to the version I am running?
              This should not be the case.... I tried to replicate the same as above mentioned by you and was able to see the selection made and can change/edit also.

              I am using JDeveloper 11.1.1.6.0. may suggest you to use latest one or you can try to create a new DB Adapter after restarting JDeveloper.

              Regards,
              Abhinav Gupta
              • 4. Re: DBadapter on the Hr.employees table
                Stuart Fleming
                Abhinav,
                I am on the newest version. I will try re-creating the dbadapter. I am on the latest version, but who knows if there is something wrong with the installation.

                I appreciate the help!!

                Best Regards,
                Stuart
                • 5. Re: DBadapter on the Hr.employees table
                  Stuart Fleming
                  GEES!!! I didn't even notice the "execute pure SQL" selection!!!

                  Thanks!!

                  DUH!

                  Stuart
                  • 6. Re: DBadapter on the Hr.employees table
                    Stuart Fleming
                    Thank you very much!