Hi Jack,
If it works as expected, option 1 is no doubt the neatest. However, remember
that this is an undocumented feature, so play with it by all means, but don't
build it into anything important. If you were to hit problems (like your
ORA-3113 error) once it was in production, you would have a difficult time
getting Oracle support to listen to you!
Let us know if you find anything else interesting. Remember don't rely on the
default database order in which records are returned.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Friday, 16 February 2001 9:37
To: Steve Adams; [EMAIL PROTECTED]
Steve,
Yes, it does work, but not on our Documents table, which is
partitioned! After getting your reply I tried it again on
another table (Documents is our main table and I just tend
to have Documents on the brain - that's why I picked it
originally) and it worked on the other, non-partitioned
table!
Now, I'm assuming that it's because Documents is partitioned
that I get the ORA-03113, but it also has a CLOB column with
an Intermedia index on it. Also, the Document_ID column is
the PK for Documents, but it is a Global Index, not Local.
So I wonder what it is exactly about Documents that causes
the ORA-03113?
Also, I noticed that the locked rows that are skipped by the
2nd session are counted when satisfying the RowNum
predicate. That is, to get two rows returned from the 1st
session's query, I specify RowNum < 3. However, to get two
unlocked rows in the 2nd session's query, I must specify
RowNum < 5, otherwise I get "no rows selected".
That kind of defeats its usefulness if I want to use the
same query in several sessions to return just two unlocked
rows in each - I'd have to know how many other sessions had
issued the statement to specify the right RowNum figure.
Any ideas on how to work around that?
The only things I can think of are:
1. Putting the Select in a Cursor and Looping through it
until I get the number of unlocked rows I want. This is
probably the best, but I need to test it.
2. Using a Sequence, from which each session would get
NextVal, thus providing a count for each succeeding session
to set its RowNum. Coordinating the fetching of NextVal
from the sequence by several, perhaps, parallel sessions,
not to mention creation and dropping of the sequence by the
first and last sessions, could get tricky, though.
3. Inserting, updating (incrementing), then deleting a row
in a "scratch" table. An 8i Temporary Table wouldn't work
since the rows for each session wouldn't be visible to the
other sessions.
Fun stuff to explore!
Thanks.
Jack
--------------------------------
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
-----Original Message-----
Hi Jack,
After having created a suitable table, a cut and paste of
that SQL returns "no
rows selected" running 8.1.6 on NT. No error message. I've
used it under 8.1.6
on Solaris before, and it has worked there too.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
