Hello
I've searched on the forums and on the Oracle wiki for information about this exception when trying to refer to a sequence in a view. I accept that there's nothing I can change about the behaviour but I was wondering if anyone can shed light on
why a sequence cannot be used in a view?
It is possible to use a sequence in a materialized view, but it cannot be marked as BUILD DEFERRED
tylerd@DEV1> CREATE SEQUENCE dt_seq
2 /
Elapsed: 00:00:00.01
tylerd@DEV1> CREATE TABLE dt_t1
2 ( col1 VARCHAR2(10) PRIMARY KEY
3 )
4 /
Table created.
Elapsed: 00:00:00.01
tylerd@DEV1> CREATE TABLE dt_t2
2 ( col1 VARCHAR2(10) PRIMARY KEY
3 )
4 /
Table created.
Elapsed: 00:00:00.01
tylerd@DEV1> INSERT
2 INTO
3 dt_t1
4 SELECT
5 'Row '||TO_CHAR(ROWNUM)
6 FROM
7 dual
8 CONNECT BY
9 LEVEL <= 10
10 /
10 rows created.
Elapsed: 00:00:00.01
tylerd@DEV1> INSERT
2 INTO
3 dt_t2
4 SELECT
5 'Row '||TO_CHAR(ROWNUM)
6 FROM
7 dual
8 CONNECT BY
9 LEVEL <= 10
10 /
10 rows created.
Elapsed: 00:00:00.01
tylerd@DEV1> CREATE OR REPLACE VIEW dt_v1
2 AS
3 SELECT
4 dt_t1.col1 t1_col1,
5 dt_t2.col1 t2_col1
6 FROM
7 dt_t1,
8 dt_t2
9 WHERE
10 dt_t1.col1 = dt_t2.col1
11 /
View created.
Elapsed: 00:00:00.04
tylerd@DEV1> CREATE MATERIALIZED VIEW dt_mv_seq
2 AS
3 SELECT
4 t1_col1,
5 t2_col1,
6 dt_seq.NEXTVAL
7 FROM
8 dt_v1;
Materialized view created.
Elapsed: 00:00:00.48
tylerd@DEV1> select * from dt_mv_seq;
T1_COL1 T2_COL1 NEXTVAL
---------- ---------- ----------
Row 1 Row 1 21
Row 10 Row 10 22
Row 2 Row 2 23
Row 3 Row 3 24
Row 4 Row 4 25
Row 5 Row 5 26
Row 6 Row 6 27
Row 7 Row 7 28
Row 8 Row 8 29
Row 9 Row 9 30
10 rows selected.
Elapsed: 00:00:00.00
tylerd@DEV1> CREATE MATERIALIZED VIEW dt_mv_seq2
2 BUILD DEFERRED
3 AS
4 SELECT
5 t1_col1,
6 t2_col1,
7 dt_seq.NEXTVAL
8 FROM
9 dt_v1;
dt_seq.NEXTVAL
*
ERROR at line 7:
ORA-02287: sequence number not allowed here
This is really an academic question as there is nothing to be done to change the behaviour but I'm interested as it appears to be a pretty rigid restriction
tylerd@DEV1> SELECT
2 dt_seq.NEXTVAL,
3 col1
4 FROM
5 dt_t1
6 /
NEXTVAL COL1
---------- ----------
51 Row 1
52 Row 10
53 Row 2
54 Row 3
55 Row 4
56 Row 5
57 Row 6
58 Row 7
59 Row 8
60 Row 9
10 rows selected.
Elapsed: 00:00:00.00
tylerd@DEV1> SELECT
2 seq,
3 col1
4 FROM
5 ( SELECT
6 dt_seq.NEXTVAL seq,
7 col1
8 FROM
9 dt_t1
10 )
11 /
dt_seq.NEXTVAL seq,
*
ERROR at line 6:
ORA-02287: sequence number not allowed here
so maybe it will reveal something more findamental about what happens behind the scenes.
Thank you
David