Home » SQL & PL/SQL » SQL & PL/SQL » Row generator  () 1 Vote
Re: Puzzle n°00 - Row generator * [message #640208 is a reply to message #640207] Thu, 23 July 2015 10:16 Go to previous messageGo to next message
laurentschneider
Messages: 6
Registered: March 2007
Location: Switzerland
Junior Member
and if you need one million row, use clob
SELECT count(*) 
FROM JSON_TABLE(
  '['||REPLACE(LPAD(to_clob('1'),999999,1),1,'1,')||1||']',
  '$[*]'
  COLUMNS(n for ordinality, x NUMBER PATH'$'));
  COUNT(*)
----------
   1000000
Re: Puzzle n°00 - Row generator * [message #640209 is a reply to message #640208] Thu, 23 July 2015 10:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3149
Registered: May 2013
Location: World Wide on the Web
Senior Member
/forum/fa/2115/0/

I learnt something new!
Re: Puzzle n°00 - Row generator * [message #672703 is a reply to message #640209] Thu, 25 October 2018 03:46 Go to previous messageGo to next message
neiljakson
Messages: 1
Registered: October 2018
Junior Member
Good advice here. I have begun to redraft older posts just this week, so was happy to see it listed here.
Re: Puzzle n°00 - Row generator * [message #673111 is a reply to message #672703] Tue, 06 November 2018 14:17 Go to previous message
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
What version are you on? Works fine for me. And you don't need x in COLUMNS unless you plan to use it:

SQL> select version from v$instance;

VERSION
-----------------
12.2.0.1.0

SQL> SELECT n FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality, x NUMBER PATH'$'));

         N
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> SELECT n FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality));

         N
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> 

SY.
Previous Topic: Output Dummy Record When No Record Exists
Next Topic: How to use [code] tags and make your code easier to read
Goto Forum:
  


Current Time: Sat Dec 14 08:29:11 CST 2019