Oracle SQL - counting to 3

Question

This is an easy one, but I really can't figure it out.

I am trying to get this simple table:

NUMBER
1
2
3

So I try this:

select (1,2,3) as number from dual;

and I get

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

How can I create this static table?

EDIT: Sorry, I simplified my table but should have been more explicit. My numbers are not 1, 2, and 3, they are about 50 numbers somewhere between 1 and 10,000. Sounding like this can't be done?

Answer 1

This "trick" will do it:

select n
from (select rownum n from dual
      connect by level <= 3);

Answer 2

From Oracle 10g onward, you can do:

SELECT LEVEL "NUMBER"
FROM DUAL
CONNECT BY LEVEL <= 3

Obviously, this can be easily modified to generate more numbers than just 3 if needed.

--- EDIT ---

For a static list of numbers, you can simply:

SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
-- etc...

Or, in case you need to use it from more than one place, put the data in a temporary table:

CREATE GLOBAL TEMPORARY TABLE TMP_TABLE (
    "NUMBER" INT PRIMARY KEY
);

INSERT INTO TMP_TABLE VALUES (1);
INSERT INTO TMP_TABLE VALUES (2);
INSERT INTO TMP_TABLE VALUES (3);

SELECT * FROM TMP_TABLE;

Answer 3

I'm not sure what you mean by "How can I create this static table?" When I think of static tables I think of something like:

SQL> create table t (n number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (5000);

1 row created.

SQL> -- ....
SQL> insert into t values (10000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         N
----------
         1
      5000
     10000

To use dual:

SQL> select *
  2  from (select 1 as n from dual
  3      union all select 5000 from dual
  4      -- ...
  5      union all select 10000 from dual);

         N
----------
         1
      5000
     10000

Please do not name the column number. Nothing good can come from using a reserved word for as a column name.