RE: Best Way to Auto Increment with Oracle

From: Ian Harisay (imharisa_at_nuskin.com)
Date: 06/16/04

  • Next message: Steve Baldwin: "RE: Best Way to Auto Increment with Oracle" 
    Date: Wed, 16 Jun 2004 12:25:54 -0600
To: <dbi-users@perl.org>

            I would disagree with this last statement. you are gauranteed
    to get the correct value from $seq.currval in a non-threaded
    environment and your session is not shared by anything else. And if
    you are threading (say in Java. I still don't with perl) I would
    hope that each of your worker threads are not sharing a single
    session. Because if they are, you have much bigger problems than
    just getting the correct value returned to you from $seq.currval.

    >>>Reidy, Ron <Ron.Reidy@arraybiopharma.com> 06/16 2:58 am >>>
    Yes, using the SP hides the details. Getting the value just inserted
    via currval is not guaranteed to return the value you just fetched.
    Better to use 'insert ... returning col into' to get the value used
    in
    your session's insert statement.

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

    -----Original Message-----
    From: Peter J. Holzer [mailto:hjp@wsr.ac.at]
        Sent: Wednesday, June 16, 2004 9:46 AM
    To: dbi-users@perl.org
        Subject: Re: Best Way to Auto Increment with Oracle

    On 2004-06-16 09:14:25 -0600, Reidy, Ron 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.

    You can get the next value from a sequence in the insert statement
    (see
    my previous mail in this thread), so with or without the trigger,
    you
    have to parse (once) and execute (possibly many times) only the
    insert
    statement.

    The difference is:

        With a trigger, you hide the details of the sequence from the insert
        statement.
    This makes the insert statement simpler and more portable. OTOH, you
    don't know which value you just inserted (you can get at it with
    select
    $sequence.currval from dual but then you lose these advantages).

    hp 

    --
_ | Peter J.     Holzer | Shooting the users in the foot is bad.
|_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't.
| | |     hjp@wsr.ac.at | -- Gordon Schumacher,
__/ | http://www.hjp.at/ | mozilla bug #84128
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.
  • Next message: Steve Baldwin: "RE: Best Way to Auto Increment with Oracle"

    Relevant Pages

    • RE: Best Way to Auto Increment with Oracle
      ... What if some code in a trigger on the table did a NEXTVAL on the sequence? ... you are threading (say in Java. ... You then execute the insert statement. ...
      (perl.dbi.users)
    • Re: Maintaining State Between Web Pages
      ... Or between all page hits by all users? ... But I'm guessing you mean between hits in the same session. ... If you used apartment threading, ... web sites I've worked on were hosted by ISPs, it seemed pointless to use them. ...
      (microsoft.public.scripting.vbscript)
    • RE: Maintaining State Between Web Pages
      ... Or between all page hits by all users? ... But I'm guessing you mean between hits in the same session. ... If you used apartment threading, ... web sites I've worked on were hosted by ISPs, it seemed pointless to use them. ...
      (microsoft.public.scripting.vbscript)
    • Re: How to execute a aspx page more than once simultanously?
      ... > hi Patrice, ... I want to execute the 3 parts simultaniously. ... > use threading. ... >>> regards, ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Windows Service and TCP Listener
      ... My code was trying to execute once and once only. ... I corrected the define for the listener and everything works like a champ. ... > seconds the TimerFired method is invoked via the event on the timer. ... > you are having threading issues, ...
      (microsoft.public.dotnet.distributed_apps)