Re: Reproduce mysql "auto increment" in Oracle, using Perl?

Thanks for your input. I'm not sure if this will solve my problem, however.

Here's what I do today with perl and mysql:

• I INSERT a row in TABLE X. TABLE X has a MySQL auto increment column so I insert that columns value as NULL.
• MySQL set the appropriate auto increment value for the column and returns this value to Perl. That is, whatever value MySql used for the auto increment column is now available in Perl for my use.
• I then use this value to INSERT records into TABLE Y and TABLE Z.

The problem with me using sequences is that I do not think Oracle returns the value to Perl. I can not query this new row for the sequence value used because the other columns are not unique. I don't believe I can accurately query the sequence directly unless I:

 lock table
   insert row
   select current sequence val
 unlock table

and I prefer not to lock tables. Any ideas?

QM

In article <kw2A5.192$Ya.46389_at_wdc-read-01.qwest.net>,   "Mike Dwyer" <dwyermj_at_co,larimer.co.us> wrote:
> Sequences. Here is an answer I posted earlier today on
> http://www.computer-mentors.co.uk/wwwboard/oracle (answering a
 slightly
> different question).
>
> select last_number from user_sequences where sequence_name =
> 'SAL_EMPLOYEE_S';
>
> LAST_NUMBER
> -----------
> 6047
>
> select sal_employee_s.nextval from dual;
>
> NEXTVAL
> ----------
> 6047
>
> c/next/curr/
> 1* select sal_employee_s.currval from dual
> /
>
> CURRVAL
> ----------
> 6047
>
> select last_number from user_sequences where sequence_name =
> 'SAL_EMPLOYEE_S';
>
> LAST_NUMBER
> -----------
> 6048
>
> BTW, the sequence was created with:
> CREATE SEQUENCE SAL_EMPLOYEE_S START WITH 1; -- "start with 1" is
 optional
>
> HTH
> - Mike
>
> <uqmoore_at_my-deja.com> wrote in message
 news:8qo13o$uck$1_at_nnrp1.deja.com...
> > What's the best way to mimic mysql's "auto increment" feature in
 Oracle,
> > using Perl?
> >
> > In mysql, you can set a column as "auto increment". Thus, when you
> > insert a new row and specify NULL for this auto increment column's
> > value, it automatically gives this column the integer value of the
> > previously submitted row, plus one.
> >
> > The other neat thing is that mysql returns this column's value back
 to
> > Perl ($sth->{'insertid'}).
> >
> > I am familiar with Oracle sequences but I do not know how to get the
> > sequence value that was inserted with the new record back to Perl.
 I
> > don't believe I can simply query the sequence's current value since
> > there's no guarantee that another insert won't happen before this
 query
> > (right?). I prefer not to lock the table. Any ideas?!?!? Thanks
 in
> > advance.
> >
> > QM
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 27 2000 - 09:04:24 CDT