Oracle 11gR2: Generate Number Series Using Select Command with array(x) as clause? UNDEF Command to Reset && variable

It is a new feature in Oracle 11gR2; you can generate number series in single select command. See some examples below.

How to generate multiplication table using Select Command?
Multiplication table for 4:
SQL> UNDEF tablefor
SQL> l
   with t(slno,tfor) as (select 1 slno, &&tablefor tfor from dual
   union all
   select slno+1, tfor+&&tablefor from t where slno<10 )
   select slno,'x &&tablefor', tfor from t
SQL> /

      SLNO 'X4       TFOR
---------- --- ----------
         1 x 4          4
         2 x 4          8
         3 x 4         12
         4 x 4         16
         5 x 4         20
         6 x 4         24
         7 x 4         28
         8 x 4         32
         9 x 4         36
        10 x 4         40
Note: UNDEF Command on SQL*Plus is used for resetting the value for && variable.

Multiplication table for 4:
SQL> UNDEF tablefor
SQL>
      with t(slno,tfor) as (select 1 slno, &&tablefor tfor from dual
      union all
      select slno+1, tfor+&&tablefor from t where slno<10 )
      select slno,'x &&tablefor', tfor from t

      SLNO 'X5       TFOR
---------- --- ----------
         1 x 5          5
         2 x 5         10
         3 x 5         15
         4 x 5         20
         5 x 5         25
         6 x 5         30
         7 x 5         35
         8 x 5         40
         9 x 5         45
        10 x 5         50

A simple number series
  with t(count) as (select 1 from dual
  union all
  select count+1 from t where count<10 )
  select count from t
SQL> /

     COUNT
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google