locking issue with select for update, sql advice requested

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

locking issue with select for update, sql advice requested

locking issue with select for update, sql advice requested

2005-10-12 - By rjamya

Reply: 1 2 3 4 5 6 7

What db version?? I probably missed that in op.

Raj

On 10/12/05, laura pena <lizzpenaorclgrp@(protected)> wrote:
>
>
> Here is the issue with this sql:
> 1 select *
> 2 from (
> 3 select callid
> 4 , to_char(calldate,'MMDDYYYY HH24:MM:SS')
> 5 from calldetail_tmp
> 6 where calldate between sysdate - 110 and
> sysdate
> 7 and audiostate = 10
> 8 and callflowtypeid in (8,13,17,28)
> 9 order by calldate
> 10 )
> 11* where rownum <= 4
> SQL> /
>
> CALLID TO_CHAR(CALLDATE,
> -- ---- ---- --- -- ---- ---- ----
> 5020050630008657 06302005 09:06:24
> 5020050630008658 06302005 09:06:30
> 5020050630008659 06302005 10:06:13
> 5020050630008660 06302005 10:06:58
>
> okay so lets add the for udpate now ....
>
>
> Add the for update:
> 1 select *
> 2 from (
> 3 select callid
> 4 , to_char(calldate,'MMDDYYYY HH24:MM:SS')
> 5 from calldetail_tmp
> 6 where calldate between sysdate - 110 and
> sysdate
> 7 and audiostate = 10
> 8 and callflowtypeid in (8,13,17,28)
> 9 order by calldate
> 10 )
> 11 where rownum <= 4
> 12* for update
>
>
> ERROR at line 2:
> ORA-02014 (See ORA-02014.ora-code.com): cannot select FOR UPDATE from view with
> DISTINCT, GROUP BY, etc.
>
>
> Issue with locking is the application is
> multi-threaded , if I break up this up into 2 sql
> statements two threads could update the same row. I
> don't want this to happen.
>
> Many Thanks,
> -Lizz
>
> --- Anthony Wilson <amwilson@(protected)> wrote:
>
> > 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
> >
>
>
>
>
>
> __ ____ ____ ____ ____ ____ ______
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>

--
-- ---- ---- ---- ---- ---- --
select standard_disclaimer from company_requirements where category =
'MANDATORY';

What db version?? I probably missed that in op. Raj <div>On 10/12/05, laura pena &lt
;<a href="mailto:lizzpenaorclgrp@(protected)">lizzpenaorclgrp@(protected)
</a>> wrote:<blockquote class="gmail_quote" style="border-left: 1px
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Here is the issue with this sql:   1  select *  
 2  from ( 
  3      select callid   4
      , to_char(calldate,'MMDDYYYY HH24:MM:SS')   5      from calldetail_tmp  
 6      where calldate between sysdate - 110
and sysdate   7      and
audiostate = 10   8      and
callflowtypeid in (8,13,17,28)
   9      order by calldate 10
  ) 11* where rownum <= 4 SQL> / CALLID &nbsp
;         TO_CHAR(CALLDATE, -- ------
-- ---- -- ---- ---- ---- 5020050630008657 06302005 09:06:24 5020050630008658 06302005 09:06:30
 5020050630008659 06302005 10:06:13 5020050630008660 06302005 10:06:58 okay so lets add the for udpate now .... Add the for update: 
1  select * 2  from ( 3    
  select callid 4      , to_char
(calldate,'MMDDYYYY HH24:MM:SS')
 5      from calldetail_tmp 6  
    where calldate between sysdate - 110 and sysdate 
7      and audiostate = 10 8  &nbsp
;   and callflowtypeid in (8,13,17,28) 9   
   order by calldate 10  ) 11  where
rownum <= 4
 12* for update ERROR at line 2: ORA-02014 (See ORA-02014.ora-code.com): cannot select FOR
UPDATE from view with DISTINCT, GROUP BY, etc. Issue with locking
is the application is multi-threaded , if I break up this up into 2 sql
 statements two threads could update the same row. I don't want this to
happen. Many Thanks, -Lizz --- Anthony Wilson <<a href=
"mailto:amwilson@(protected)">amwilson@(protected)</a>> wrote: 
 > 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 &gt
; 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 > 
 __ ____ ____ ____ ____ ____ ______ Yahoo! Mail - PC Magazine Editors'
Choice 2005 <a href="http://mail.yahoo.com">http://mail.yahoo.com</a> --
 <a href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org
/webpage/oracle-l
</a> </blockquote></div> -- -- ---- ---- ---- ---
-- ------ select standard_disclaimer from company_requirements where category
= 'MANDATORY';