RE: SELECT SKIP

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Thu, 15 Feb 2001 13:53:08 -0800
Message-ID: <F001.002B5F14.20010215134952@fatcity.com>

Steve,

Here's what I did to test Select...For Update Skip Locked.

The same ORA-03113 happens running SQL*Plus 8.1.6 or 8.1.7
(both window and command line versions) against either
Oracle8i EE 8.1.6.0.0 or 8.1.7.0.0. SQL*Plus and the databases are running under Windows2000. Both the simple Select and the PL/SQL block work (or wait) as expected if the "Skip Locked" phrase is left off and either NoWait or Wait is specified.

First, in SQL*Plus session 1:

SQL> Select Document_ID
  2 from Documents
  3 Where RowNum < 3
  4 For Update;

DOCUMENT_ID

9789959
9789960

Then, in SQL*Plus session 2:

SQL> Select Document_ID
  2 from Documents
  3 Where RowNum < 3
  4 For Update Skip Locked ;
Select Document_ID
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Even putting the Select in a PL/SQL anonymous block doesn't help.
In SQL*Plus session 2:

Declare

  Type rc Is Ref Cursor ;
  c    rc               ;
  n    Number           ;

Begin
  Open c For
  'Select Document_ID From Documents Where RowNum < 3 For Update Skip Locked' ;
  Loop
    Fetch c Into n ;
    Exit When c%NotFound ;
    DBMS_Output.Put_Line('Document_ID= ' || To_Char(n)) ;   End Loop ;
  Close c ;
End;
/

Does it work on other OSs or Oracle versions? Am I missing something?

Thanks.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
japplewhite_at_inetprofit.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: japplewhite_at_inetprofit.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Feb 15 2001 - 15:53:08 CST