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 ProductionWith the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining optionsJServer 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 Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
