RE: Best Way to Auto Increment with Oracle

From: Ron Reidy (Ron.Reidy_at_arraybiopharma.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 09:39:07 -0600
To: "David Goodman" <dtzgdman@yahoo.com>, <dbi-users@perl.org>

<y preference is to use SPs wherever possible and avoid as much network interaction as possible. I saw a follow up post to the original after I had sent me reply where the responder suggested a statement like 'insert into tab values (select seq.nextval, col, col from dual)'. This would certainly work, but my preference would be to remove this detail from the main flow of code. Some developers I have worked with prefer to fetch the sequences themselves. As a DBA, I advocate strongly the use of SPs and before insert triggers.

-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.

-----Original Message-----
From: David Goodman [mailto:dtzgdman@yahoo.com]
Sent: Wednesday, June 16, 2004 9:27 AM
To: dbi-users@perl.org
Subject: RE: Best Way to Auto Increment with Oracle

Hello Ron:

Let me naively ask, why you would not use a stored
procedure to get rid of the network interaction? That
or just bundle up the two statements in one execution.

regards,

David

--- "Reidy, Ron" <Ron.Reidy@arraybiopharma.com> wrote:
> Better? Maybe. Consider.
>
> Your program connects via SQL*net to the DB. At
> insert time, you need to fetch a sequence, so you
> issue 'select seq.nextval from dual'. Seems
> harmless enough until ...
>
> 1. The select statement must be passed across the
> SQL*Net connection.
> 2. The statement must be parsed. Maybe the worst
> you will have here is a soft parse, but non the
> less, you are using the CPU for this.
> 3. The statement is executed and the fetch occurs.
> 4. The result is passed back to your program.
> 5. You bind the value (hopefully) to your statement
> handle.
> 6. You then execute the insert statement.
>
> If you use a BEFORE INSERT trigger to fetch the
> sequence, you will eliminate steps 1, 2, 3, 4, 5
> from the flow.
>
> -----------------
> Ron Reidy
> Senior DBA
> Array BioPharma, Inc.
>
>
> -----Original Message-----
> From: Jim [mailto:nep_tuna@yahoo.com]
> Sent: Wednesday, June 16, 2004 6:21 AM
> To: dbi-users@perl.org
> Subject: Best Way to Auto Increment with Oracle
>
>
> Hi
> I am looking for suggestions on the best way to auto
> increment a key column in a oracle table. I know I
> can
> do this inside of Oracle by creating a sequence and
> then a trigger. BUT, is it better to make DBI do
> this
> when I insert the data? If so, how would I go about
> it?
> Thanks
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail Address AutoComplete - You start. We
> finish.
> http://promotions.yahoo.com/new_mail
>
> This electronic message transmission is a PRIVATE
> communication which contains
> information which may be confidential or privileged.
> The information is intended
> to be for the use of the individual or entity named
> above. If you are not the
> intended recipient, please be aware that any
> disclosure, copying, distribution
> or use of the contents of this information is
> prohibited. Please notify the
> sender of the delivery error by replying to this
> message, or notify us by
> telephone (877-633-2436, ext. 0), and then delete it
> from your system.
>
>

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Relevant Pages

  • RE: perl Oracle
    ... You can do a silent install. ... See the Oracle installation docs. ... > Ron Reidy ... > by replying to this message, or notify us by telephone (877-633-2436, ext. ...
    (perl.dbi.users)
  • RE: Insights into DBI->connect differences
    ... You also should set your oracle environment using the shell script ... the sender of the delivery error by replying to this message, or notify ... This electronic message transmission is a PRIVATE communication which contains ... intended recipient, please be aware that any disclosure, copying, distribution ...
    (perl.dbi.users)
  • RE: Connect to Oracle
    ... Subject: Connect to Oracle ... This electronic message transmission is a PRIVATE communication which ... Please notify the sender of the delivery ... intended recipient, please be aware that any disclosure, copying, ...
    (perl.dbi.users)
  • Re: Connect to Oracle
    ... Subject: Connect to Oracle ... This electronic message transmission is a PRIVATE communication which ... Please notify the sender of the delivery ... intended recipient, please be aware that any disclosure, copying, ...
    (perl.dbi.users)
  • RE: oracle delete has no effect
    ... did you commit the transaction before you ran your Perl program? ... oracle delete has no effect ... > sender of the delivery error by replying to this message, or notify us by ... intended recipient, please be aware that any disclosure, copying, distribution ...
    (perl.dbi.users)