This discussion is archived
2 Replies Latest reply: Oct 1, 2009 7:52 AM by 843830 RSS

Mixing sun-database-binding with Oracle sequences

843830 Newbie
Currently Being Moderated
Hello,

I wish to insert rows inside the database, for instance rows representing persons, and rows representing their addresses. Primary keys for both person and address are Oracle sequence based.

My question is, if I insert a person, then one of her address, how can I retrieve the person ID for the address row to reference it.

The insert statement for the person is (for the moment) as follow
insert into Person (PERSON_ID, FIRST_NAME, LAST_NAME) values (PERSON_SEQ.nextval, ?, ?)
The problem with this approach is that I never know the person ID, and am unable to make any references to it in the address row.

I tried to add an operation in the NetBeans generated person table WSDL. This operation would execute this statement:
select PERSON_SEQ.nextval from dual
But, for the moment, it's a failure.

Could you provide me with some hints?
  • 1. Re: Mixing sun-database-binding with Oracle sequences
    843830 Newbie
    Currently Being Moderated
    Hi,

    First I would advise you to register and post on the users@open-esb.dev.java.net alias - you can find the details of how to do this on the OpenESB site..... you'll reach a larger audience this way.

    Second, you need a Stored Procedure in Oracle to do this, then use this from the DB BC, here's one I created which does something similar, i.e. returns a value I'm interested in after an "Update" statement....

    CREATE OR REPLACE PROCEDURE "NEXTAPPNUMFINDER" (nextAppNum OUT NUMBER)
    IS
    BEGIN
    UPDATE ACTIVE_APPLICATION_NUMBER
    SET APPLICATION_NUMBER_NEXT = APPLICATION_NUMBER_NEXT + 1
    RETURNING APPLICATION_NUMBER_NEXT
    INTO nextAppNum;
    END;

    Hope this helps
    Mark
  • 2. Re: Mixing sun-database-binding with Oracle sequences
    843830 Newbie
    Currently Being Moderated
    Thank you very much for your advice and solution. I'll try to take advantage of them the best I can.