0

I am looking for an alternative to CONNECT BY:

Select SUBSTR(str,1,LEVEL) OUTPUT FROM
(
    SELECT 'ORACLE'  As str FROM DUAL
)
CONNECT BY LEVEL<=LENGTH(str);

Any suggestions?

Tripp Kinetics
  • 5,178
  • 2
  • 23
  • 37
MastanSky
  • 57
  • 1
  • 10
  • 4
    What is the issue with using a `connect by`? We can suggest alternatives like, say, a pipelined table function. But without knowing what problem you're trying to solve, it's very very difficult to guess at what alternatives will be better for you rather than worse. – Justin Cave Oct 12 '15 at 18:04

3 Answers3

6

Several options:

SQL Fiddle

Query 1 - Use a collection:

The values are hard-coded here so this probably isn't the best solution unless you have a fixed length list.

SELECT SUBSTR( 'ORACLE', 1, COLUMN_VALUE ) AS value
FROM   TABLE( SYS.ODCINUMBERLIST( 1, 2, 3, 4, 5, 6 ) )

Results:

|  VALUE |
|--------|
|      O |
|     OR |
|    ORA |
|   ORAC |
|  ORACL |
| ORACLE |

Query 2 - Use a pipelined function to generate a collection:

CREATE OR REPLACE FUNCTION get_numbers (
  maximum IN NUMBER
) RETURN SYS.ODCINUMBERLIST PIPELINED AS
BEGIN
  FOR i IN 1 .. maximum LOOP
    PIPE ROW(i);   
  END LOOP;
  RETURN;
END;
/

SELECT SUBSTR( 'ORACLE', 1, COLUMN_VALUE ) AS value
FROM   TABLE( get_numbers( LENGTH( 'ORACLE' ) ) )

Results:

|  VALUE |
|--------|
|      O |
|     OR |
|    ORA |
|   ORAC |
|  ORACL |
| ORACLE |

Query 3 - Use a non-pipelined function to generate a collection:

CREATE OR REPLACE FUNCTION get_numbers2 (
  maximum IN NUMBER
) RETURN SYS.ODCINUMBERLIST
AS
  v_nums SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
BEGIN
  v_nums.EXTEND( maximum );
  FOR i IN 1 .. maximum LOOP
    v_nums(i) := i;   
  END LOOP;
  RETURN v_nums;
END;
/

SELECT SUBSTR( 'ORACLE', 1, COLUMN_VALUE ) AS value
FROM   TABLE( get_numbers2( LENGTH( 'ORACLE' ) ) )

Results:

|  VALUE |
|--------|
|      O |
|     OR |
|    ORA |
|   ORAC |
|  ORACL |
| ORACLE |

Query 4 - Use Recursive Subquery Factoring:

WITH data ( value ) AS (
  SELECT 'ORACLE' FROM DUAL
  UNION ALL
  SELECT SUBSTR( value, 1, LENGTH( value ) - 1 )
  FROM   data
  WHERE  LENGTH( value ) >  1
)
SELECT * FROM data

Results:

|  VALUE |
|--------|
| ORACLE |
|  ORACL |
|   ORAC |
|    ORA |
|     OR |
|      O |
MT0
  • 143,790
  • 11
  • 59
  • 117
-3

SELECT RPAD(LPAD('ORACLE',ROWNUM,'ORACLE'),7) AS GRAPH from emp where rownum <7

Santu.
  • 33
  • 1
  • 8
-3

Here is a pyramid programme.

SELECT RPAD(LPAD('ILOVEYOU',ROWNUM,'ILOVEYOU'),9) ||LPAD(RPAD('ILOVEYOU',ROWNUM,'ILOVEYOU'),9)LOVEGRAPH FROM EMP WHERE ROWNUM<9 UNION ALL SELECT * FROM(SELECT RPAD(LPAD('ILOVEYOU',ROWNUM,'ILOVEYOU'),9) ||LPAD(RPAD('ILOVEYOU',ROWNUM,'ILOVEYOU'),9)LOVEGRAPH FROM EMP WHERE ROWNUM<9 ORDER BY ROWNUM DESC) /

LOVEGRAPH

I I IL IL ILO ILO ILOV ILOV ILOVE ILOVE ILOVEY ILOVEY ILOVEYO ILOVEYO ILOVEYOU ILOVEYOU ILOVEYOU ILOVEYOU ILOVEYO ILOVEYO ILOVEY ILOVEY ILOVE ILOVE ILOV ILOV ILO ILO IL IL I I

16 rows selected

Santu.
  • 33
  • 1
  • 8