Thanks,
David Y. Hodge
On 9/15/06, Larry Meadors <[EMAIL PROTECTED]
> wrote:
In that case, you may be able to do it as anonymous pl/sql. But I
think I'd try the selectKey way first...but that anon pl/sql might be
faster - just one call to the database instead of two.
Bah, try them both, and use what works best for you. ;-)
Larry
On 9/15/06, David Hodge <[EMAIL PROTECTED] > wrote:
> The trigger creates the Primary Key before the INSERT. There is no sequence
> and if I did specify the key it would be overridden by the trigger. I
> thought I could maybe use the RETURNING INTO clause where I specify a
> variable to return the primary key into. The SQL works but I am not sure
> how to get it back so that iBatis can use it or map it.
>
>
> Thanks,
>
> David Y. Hodge
>
> On 9/15/06, Crocker, Patrick <[EMAIL PROTECTED]> wrote:
> > An immediate call to "SELECT your_sequence_name.currval FROM dual" after
> > the insert?
> >
> > Or...
> >
> > How is the trigger setup? Does it override the value if you specify it
> > yourself?
> >
> > This trigger (from
> > http://www.oracle-base.com/articles/8i/AutoNumber.php )
> will get the next
> > sequence if NOT specified in the insert. This would allow you to get
> > the nextval yourself and pass it in to the insert statement:
> >
> > CREATE OR REPLACE TRIGGER dept_bir
> > BEFORE INSERT ON departments
> > FOR EACH ROW
> > WHEN ( new.id IS NULL)
> > BEGIN
> > SELECT dept_seq.NEXTVAL
> > INTO :new.id
> > FROM dual;
> > END;
> > /
> >
> > - Patrick.
> >
> > ________________________________
> >
> > From: David Hodge [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, September 15, 2006 8:37 AM
> > To: [email protected]
> > Subject: Re: Obtaining Generated Primary Keys
> >
> >
> > The Problem is that I can not use a sequence, as the key is generated
> > via a Trigger. Unfortunately, I do not have control over this trigger.
> > Otherwise, what you said would work perfectly.
> >
> > Thanks,
> >
> > David Y. Hodge
> >
> >
> > On 9/15/06, Crocker, Patrick <[EMAIL PROTECTED]> wrote:
> >
> > Using Oracle Sequences, you need to obtain the key first, then
> > use it in
> > the INSERT statement:
> >
> > <select id="getNextId" resultClass="decimal">
> > SELECT your_sequence_name.nextval AS nextid FROM dual
> > </select
> >
> > <insert id="insert" parameterClass="myTable">
> > INSERT INTO my_table
> > (MY_ID, MY_COLUMN)
> > VALUES
> > (#myId#, #myColumn#)
> > </insert>
> >
> > - Patrick.
> >
> > ________________________________
> >
> > From: David Hodge [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, September 15, 2006 6:58 AM
> > To: [email protected]
> > Subject: Obtaining Generated Primary Keys
> >
> >
> > I am using Oracle and I am wondering if there is a way to obtain
> > a
> > trigger generated Primary key after doing an INSERT with iBatis.
> >
> > I know there is a selectKey function but that seems to work only
> > if you
> > have a select statement, which makes sense. Other databases
> > have ways
> > for you to select the last_generated_id() but I do not think
> > Oracle has
> > this capability.
> >
> > Has anyone else had this problem or know of a work around?
> >
> >
> > Thanks,
> >
> > David Y. Hodge
> >
> >
> >
> >
> >
>
>
