TABLE functions in Oracle 12.2

Oracle 12.2 introduced a nice little feature – when selecting from a table-function you no longer have to use the TABLE function:

Before 12.2, here is what you had to do:

SELECT *
FROM TABLE([your-collection-or-table-function])

Starting with 12.2 the code becomes shorter:

SELECT *
FROM [your-collection-or-table-function]

Example (Oracle 12.2):

SELECT *
FROM sys.odcinumberlist(5,2,6,3,78)

Result:

COLUMN_VALUE
------------
           5
           2
           6
           3
          78

Note that if you try the last query in Oracle 12.1 or any prior release, you will get the following error:

FROM sys.odcinumberlist(5,2,6,3,78)
                       *
ERROR at line 2:
ORA-00933: SQL command not properly ended

This is an indication that you may need to check your Oracle version:

SELECT *
FROM v$version
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

Advertisements

4 Solutions to Puzzle of the Week #12

Puzzle of the Week #12

With a single SELECT statement produce a list of first 10 prime numbers above a given number of N.

Expected Result: (for N=15)

     Prime
----------
        17
        19
        23
        29
        31
        37
        41
        43
        47
        53

10 rows selected.

Expected Result: (for N=50)

     Prime
----------
        53
        59
        61
        67
        71
        73
        79
        83
        89
        97

10 rows selected.

Solutions:

#1: Liming number of found prime numbers in CTE (Recursive WITH clsue)

WITH y AS (
SELECT 500 fromN
FROM dual
), x (n, cnt, flag) AS (
SELECT fromN,
      (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
       FROM dual
       WHERE MOD(fromN, LEVEL)=0
       CONNECT BY LEVEL<=fromN),
      (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
       FROM dual
       WHERE MOD(fromN, LEVEL)=0
       CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT x.cnt+CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
               FROM dual
               WHERE MOD(x.n+1, LEVEL)=0
               CONNECT BY LEVEL<=x.n+1),
              (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
               FROM dual
               WHERE MOD(x.n+1, LEVEL)=0
               CONNECT BY LEVEL<=x.n+1)
FROM x
WHERE x.cnt

#2: Limiting number of found prime numbers outside of CTE (Recursive WITH clsue)

WITH y AS (
SELECT 50 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN,
      (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
       FROM dual
       WHERE MOD(fromN, LEVEL)=0
       CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
               FROM dual
               WHERE MOD(x.n+1, LEVEL)=0
               CONNECT BY LEVEL<=x.n+1) FROM x WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
  AND ROWNUM<=10;

     PRIME
----------
        53
        59
        61
        67
        71
        73
        79
        83
        89
        97

10 rows selected.

Elapsed: 00:00:00.02

#3: Using TABLE and MULTISET functions

WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, column_value flag 
FROM y, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
                            FROM dual
                            WHERE MOD(fromN, LEVEL)=0
                            CONNECT BY LEVEL<=fromN) AS sys.odcinumberlist))  
UNION ALL
SELECT x.n+1, column_value flag  
FROM x, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
                            FROM dual
                            WHERE MOD(x.n+1, LEVEL)=0
                            CONNECT BY LEVEL<=x.n+1) AS sys.odcinumberlist))  WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
  AND ROWNUM<=10;

     PRIME
----------
        17
        19
        23
        29
        31
        37
        41
        43
        47
        53

10 rows selected.

Elapsed: 00:00:00.12

#4: Using LATERAL views

WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, is_prime
FROM y, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
                 FROM dual
                 WHERE MOD(fromN, LEVEL)=0
                 CONNECT BY LEVEL<=fromN)
UNION ALL
SELECT x.n+1, is_prime 
FROM x, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
                 FROM dual
                 WHERE MOD(x.n+1, LEVEL)=0
                 CONNECT BY LEVEL<=x.n+1) WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
 AND ROWNUM<=10;

     PRIME
----------
        17
        19
        23
        29
        31
        37
        41
        43
        47
        53

10 rows selected.

Elapsed: 00:00:00.11

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.