Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Monday dumb question...

RE: Monday dumb question...

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 9 Jan 2007 12:59:30 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A95E4558@usahm208.amer.corp.eds.com>


Yes, a test shows the statement does work as part of a CTAS on 9.2.0.6 AIX 5.2 It also works as the query in an insert select statement.  

pat1 > insert into mark select rownum -1 id from dual connect by level <= 10;  

10 rows created.  

Thanks for the updates.

	From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] 
	Sent: Tuesday, January 09, 2007 12:36 PM
	To: Powell, Mark D
	Cc: oracle-l_at_freelists.org
	Subject: RE: Monday dumb question...
	
	
	Jared pointed that out to me privately. It DOES work in the
context of a CTAS or insert ... select which is what I understood the OP wanted. I was just too lazy to type more than the select part. I use that trick al the time for test cases.         

        10:33:34 ora92.scott> select banner from v$version;         

	BANNER

----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production PL/SQL Release 9.2.0.7.0 - Production CORE 9.2.0.7.0 Production TNS for 32-bit Windows: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production 5 rows selected. 10:33:58 ora92.scott> create table mark as select rownum id from
dual connect by level <= 10;         

        Table created.         

        10:34:26 ora92.scott> select * from mark;         

	        ID

----------
1 2 3 4 5 6 7 8 9 10 10 rows selected. 10:34:31 ora92.scott> insert into mark select rownum+1000 id
from dual connect by level <= 15;         

        15 rows created.         

        10:34:58 ora92.scott> select * from mark;         

	        ID

----------
1 2 3 4 5 6 7 8 9 10 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 25 rows selected. 10:35:03 ora92.scott> That's what you get for being lazy. Now I had to type even more. At 10:18 AM 1/9/2007, Powell, Mark D wrote: This proposed solution does not appear to work on
9.2.0.6 running on AIX 5.2                  
		Connected to:
		Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit
Production
		With the Partitioning, Real Application Clusters, OLAP
and Oracle Data Mining options
		JServer Release 9.2.0.6.0 - Production
		 
		UT1 > select rownum-1 from dual connect by level <= 100;
		 
		  ROWNUM-1
		----------
		         0
		 
		

	

	Regards
	
	Wolfgang Breitling
	Centrex Consulting Corporation
	www.centrexcc.com <http://www.centrexcc.com/>  
	
______________________________________________________________________
	This email has been scanned by the MessageLabs Email Security
System.
	For more information please visit

http://www.messagelabs.com/email         
        
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 09 2007 - 11:59:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US