Re: locking issue with select for update, sql advice requested
- From: Anthony Wilson <amwilson@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Oct 2005 00:13:02 +0800
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
Other related posts:
