From oracle-l-bounce@freelists.org Wed Oct 12 11:16:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9CGGJOn006042 for ; Wed, 12 Oct 2005 11:16:19 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9CGGDvX006014 for ; Wed, 12 Oct 2005 11:16:14 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A0331FD293; Wed, 12 Oct 2005 11:16:07 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17676-02; Wed, 12 Oct 2005 11:16:07 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DDD911FD148; Wed, 12 Oct 2005 11:16:06 -0500 (EST) X-BrightmailFiltered: true X-Brightmail-Tracker: AAAAAA== Date: Thu, 13 Oct 2005 00:13:02 +0800 From: Anthony Wilson To: oracle-l@freelists.org Subject: Re: locking issue with select for update, sql advice requested Message-ID: <20051012161302.GA14912@iinet.net.au> References: <20051012153424.54709.qmail@web35007.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="envbJBWh7q8WU6mo" Content-Disposition: inline In-Reply-To: <20051012153424.54709.qmail@web35007.mail.mud.yahoo.com> User-Agent: Mutt/1.5.6+20040907i X-archive-position: 26812 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: amwilson@iinet.net.au Precedence: normal Reply-To: amwilson@iinet.net.au X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham version=2.63 --envbJBWh7q8WU6mo Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable For the ordering issue, just move the rownum predicate out of the query: select * =66rom ( select callid , to_char(calldate,'MMDDYYYY HH24:MM:SS') from calldetail_tmp where calldate between sysdate - 110 and sysdate and audiostate =3D 10 and callflowtypeid (8,13,17,28) order by calldate ) where rownum <=3D 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. >=20 > Here is the sql: >=20 > select callid, TO_CHAR(calldate,'MMDDYYYY HH24:MM:SS') > from calldetail_tmp > where calldate between sysdate -110 and sysdate > and (audiostate =3D 10 AND callflowtypeid IN > (8,13,17,28)) > and rownum <=3D 4 > order by calldate for update; >=20 >=20 > 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. >=20 > 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. >=20 >=20 > 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. >=20 >=20 > Many Thanks, > -Lizz=20 >=20 --=20 Anthony Wilson --envbJBWh7q8WU6mo Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQFDTTYOApUeo2ZHBDwRAvulAJ9gzlLQhKoOTdG5uziRskTduidPbACdFIyT azgehtzALJ0KK1vVZudJ+pU= =oYvo -----END PGP SIGNATURE----- --envbJBWh7q8WU6mo-- -- http://www.freelists.org/webpage/oracle-l