RE: dual

Do you think it will work if it has no rows ?  

Waleed

-----Original Message-----
Sent: Thursday, October 30, 2003 1:44 PM To: Multiple recipients of list ORACLE-L

You may find this interesting.

Looks like a 'where rownum = 1' is always imposed on dual.

Same results on 8.1.7.4 and 9.2.0.4

Don't try this on anything other than a trashable test database.

Jared

10:42:04 dv03>@dt 
10:42:05 dv03> 
10:42:05 dv03>set echo on 
10:42:05 dv03> 
10:42:05 dv03>create table jkstill.dual as select * from sys.dual; 

Table created.

10:42:05 dv03>
10:42:05 dv03>select * from jkstill.dual;

D
-

X

1 row selected.

10:42:05 dv03>
10:42:05 dv03>drop table jkstill.dual;

Table dropped.

10:42:05 dv03>
10:42:05 dv03>insert into sys.dual values('Y');

1 row created.

10:42:05 dv03>insert into sys.dual values('Z');

1 row created.

10:42:05 dv03>
10:42:05 dv03>commit;

Commit complete.

10:42:05 dv03>
10:42:05 dv03>select * from sys.dual;

D
-

X

1 row selected.

10:42:05 dv03>
10:42:05 dv03>create table jkstill.dual as select * from sys.dual;

Table created.

10:42:05 dv03>
10:42:05 dv03>select * from jkstill.dual;

D
-

X
Y
Z

3 rows selected.

10:42:05 dv03>
10:42:05 dv03>drop table jkstill.dual;

Table dropped.

10:42:05 dv03>
10:42:05 dv03>delete from sys.dual;

1 row deleted.

10:42:05 dv03>delete from sys.dual;

1 row deleted.

10:42:05 dv03>delete from sys.dual;

1 row deleted.

10:42:05 dv03>
10:42:05 dv03>insert into sys.dual values('X');

1 row created.

10:42:05 dv03>commit;

Commit complete.

10:42:05 dv03> 
10:42:05 dv03> 
10:42:05 dv03>create table jkstill.dual as select * from sys.dual; 

Table created.

10:42:05 dv03>
10:42:05 dv03>select * from jkstill.dual;

D
-

X

1 row selected.

10:42:05 dv03>
10:42:05 dv03>drop table jkstill.dual;

Table dropped.

10:42:05 dv03>

        <bulbultyagi_at_now-india.net.in>
Sent by: ml-errors_at_fatcity.com

 10/30/2003 08:54 AM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com> 
        cc:         
        Subject:        dual

List, here is a rtfm question which I was scared to ask, but its bothering me too much so I just can't stay quite :

"why do multiple inserts into sys.dual complete sucessfully when connected as
sysdba, but a subsequent select * from dual show only 1 row ?"

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <bulbultyagi_at_now-india.net.in
 INET: bulbultyagi_at_now-india.net.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).







-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).