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.

Mark D Powell -- Phone (313) 592-5148

	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