Cannot use sequence in a spooled UNION Query

Question

I need to spool a query with column headers. The query itself runs correctly on its own, and the dummy header query runs correctly alone. The query uses a sequence to sequentially number the rows (they represent the equivalent of order line items), and if I UNION the two queries I get 'specified sequence number (CURRVAL or NEXTVAL) is inappropriate.

Header 'query':

SELECT
 'ITEM_NUMBER,
  BM_NUMBER,
  CREATION_DATE'
FROM DUAL;

Data query:

SELECT
  seq.nextval||';'||
  BM_NUMBER||';'||
  CREATION_DATE
FROM BIG_TABLE;

Both run and return exactly what I expect.

SELECT
 'ITEM_NUMBER,
  BM_NUMBER,
  CREATION_DATE'
FROM DUAL
UNION
SELECT
  seq.nextval||';'||
  BM_NUMBER||';'||
  CREATION_DATE
FROM BIG_TABLE;

returns the error. I anticipated an implicit type conversion error, and have TO_CHAR() the sequence which made no difference and wasn't an issue. I also would expect the data query to be completely independent of the header query and therefore have no bearing on how the sequence works. In other words, I don't understand when the call to seq.nextval is aware of or cares about the header query that precedes it.

The only workaround I'm sharp enough to think of would be to first use the data query (with the sequence) to INSERT into a table, then UNION the header query with another query against that table (eliminating the need to use the sequence at all in the spooled UNION query. I'm happy to do that, but I'd like to know why my current query fails and fix it without otherwise unecessary reads/writes.

score 1 · Answer 1 · answered Jun 17 '13 at 16:14

Check this out. It describes the limitations of sequences.

They are not allowed in union. I don't know if the limitation also applies to union all. You might try:

SELECT
 'ITEM_NUMBER,
  BM_NUMBER,
  CREATION_DATE'
FROM DUAL
UNION all
SELECT
  seq.nextval||';'||
  BM_NUMBER||';'||
  CREATION_DATE
FROM BIG_TABLE;

If that doesn't work, you can use rownum instead:

SELECT
 'ITEM_NUMBER,
  BM_NUMBER,
  CREATION_DATE'
FROM DUAL
UNION all
SELECT
  cast(rownum as varchar2(255))||';'||
  BM_NUMBER||';'||
  CREATION_DATE
FROM BIG_TABLE;

You might want to have an offset variable, if the purpose in using the sequence is to handle multiple invocations of the query.

Solved. I never think to use rownum for anything. In this case it not only works, I believe it's actually the right thing to do--ITEM_NUMBER has no other meaning than that provided by rownum and there is no meaning to the order/sequence. I hope it's okay to say thank you very much here. — Kirk Fleming, Jun 17 '13 at 17:18

Cannot use sequence in a spooled UNION Query

1 Answers1