Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: locking issue with select for update, sql advice requested

Re: locking issue with select for update, sql advice requested

From: Anthony Wilson <amwilson_at_iinet.net.au>
Date: Thu, 13 Oct 2005 00:13:02 +0800
Message-ID: <20051012161302.GA14912@iinet.net.au>


For the ordering issue, just move the rownum predicate out of the query:

select *
from (

    select callid
    , to_char(calldate,'MMDDYYYY HH24:MM:SS')     from calldetail_tmp
    where calldate between sysdate - 110 and sysdate     and audiostate = 10
    and callflowtypeid (8,13,17,28)
    order by calldate
)
where rownum <= 4
/

As for the locking issue, I'm not sure I understood the question...??

cheers,
Anthony

On Wed, Oct 12, 2005 at 08:34:23AM, laura pena wrote:
> I would like to create a lock in my Java application
> using select for update.
>
> Here is the sql:
>
> select callid, TO_CHAR(calldate,'MMDDYYYY HH24:MM:SS')
> from calldetail_tmp
> where calldate between sysdate -110 and sysdate
> and (audiostate = 10 AND callflowtypeid IN
> (8,13,17,28))
> and rownum <= 4
> order by calldate for update;
>
>
> Issue hear rownum does not guarantee results will be
> in order specified by the order by clause. Rownum is
> set before sort is done by order clause.
>
> Most of the time calls are returned in sorted order (
> order of insertion is what rownum is being returned
> as). It is in a backlog condition that rows are not
> returned properly.
>
>
> I have looked at locking via a view but can not lock
> on a complex view. Am looking at lock table in share
> mode now. Any suggestions would be greatly
> appreciated.
>
>
> Many Thanks,
> -Lizz
>

-- 
Anthony Wilson


-- http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 12 2005 - 11:16:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US