5 Replies Latest reply: May 21, 2012 4:52 PM by 911160 RSS

    Return generated id from DbAdapter

    911160
      Hello,


      I have a working insert opereration on a database adapter.
      The table has a numeric primary key wich generates a new id.
      This generated ID must be returned in the SOAP response message
      the calling application needs this id.

      How to do this?
      I am using Oracle 10g Express Edition.


      Thx!
        • 1. Re: Return generated id from DbAdapter
          NarsingPumandla
          You cannot have this functionality defined at the table level; but you can call a stored procedure by passing all the field values of the table. Let the stored proc insert the data and do a select operation on the table to get the auto incrementing number generated based on other fields and return that number to the BPEL

          Hope this helps
          N
          • 2. Re: Return generated id from DbAdapter
            Swapnil Kharwadkar
            if it is really urgent then try following in xquery. Place this xquery after your DBAdapter business service publish action:

            let $sequence:= fn-bea:execute-sql ('jndi name', 'Sequence','select TABLE_NAME_SEQ.nextval from dual')
            return $sequence

            Now put this $sequence in your response.
            • 3. Re: Return generated id from DbAdapter
              911160
              Thx for your response :)

              Sorry but i dont know what you mean by "DBAdapter business service publish action"
              Where do i go in JDeveloper?
              • 4. Re: Return generated id from DbAdapter
                NarsingPumandla
                please dont confuse him, you are asking in BPEL and he is telling in OSB...

                forget that, if you are in BPEL, go for the stored procedure approach..

                Hope this helps,
                N
                • 5. Re: Return generated id from DbAdapter
                  911160
                  I am trying to do it with a stored procedure.
                  Problem is this is my first stored procedure ever written,
                  it was kind of hard to find good examples on the www.
                  create or replace procedure "CREATEFACTUUR"
                  (p_nummer IN NUMBER,
                  p_klant_id IN NUMBER,
                  p_datum IN DATE,
                  o_result OUT sys_refcursor)
                  is
                  begin
                  INSERT INTO FACTUUR (NUMMER, KLANT_ID, DATUM) VALUES (p_nummer, p_klant_id, p_datum);
                  OPEN o_result FOR SELECT * FROM factuur WHERE nummer = p_nummer AND klant_id = p_klant_id AND datum = p_datum;
                  end;
                  It compiles, and i have linked a DbAdapter to this procedure.
                  But what now? How to get the values from the result of this procedure,
                  i mean that JDeveloper sees the result and i can return it in the repsonse of the webservice.

                  Edited by: 908157 on 21-mei-2012 14:51