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

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