This discussion is archived
5 Replies Latest reply: May 21, 2012 2:52 PM by 911160 RSS

Return generated id from DbAdapter

911160 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points