Using sequence.nextval in subquery

Question

I want to create a template (via SQL) that can be copied straight into MS Excel.

Generally this went pretty well, however, I've run into a problem with the field that is supposed to store an Excel formula. This field is supposed to be used to create INSERT statements in the Excel file, based on the user input in one of the columns of the Excel sheet.

Here's my code:

SELECT
-- some other fields here
[...],
(SELECT '="INSERT INTO MyTable VALUES(" &C' || CREATE_INSERTS_TEMP_SEQ.nextval || '&",''THIS COLUMN IS TO BE FILLED BY USER IN EXCEL'',''"&D' || CREATE_INSERTS_TEMP_SEQ.currval || '&"'',14,sysdate);"' FROM DUAL) As SQL_Statement
FROM myTable;

I am getting the error message ORA-02287: sequence number not allowed here.

Why would it not be allowed in my subquery? Any idea how to fix this?

I must have access to these values in order to create a functioning Excel formula, as these numbers are the reference to the respective Excel rows...

Answer 1

The documentation includes restrictions, including:

CURRVAL and NEXTVAL cannot be used in these places:

• A subquery
• ...

But you don't need a subquery here:

SELECT
-- some other fields here
[...],
'="INSERT INTO MyTable VALUES(" &C' || CREATE_INSERTS_TEMP_SEQ.nextval
  || '&",''THIS COLUMN IS TO BE FILLED BY USER IN EXCEL'',''"&D'
  || CREATE_INSERTS_TEMP_SEQ.currval || '&"'',14,sysdate);"' As SQL_Statement
FROM myTable;

This form doesn't generate the error.

I suspect once reason it isn't allowed in a subquery is because it isn't clear, or deterministic, how many times the subquery would be executed. If it had worked in this scenario then you might have got the same insert statement for every row in your table, which presumably isn't your intention.