CODEWITHSUNDEEP
sqloracle-database
daZza
daZza

Reputation: 1689

Using sequence.nextval in subquery

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...

Upvotes: 5

Views: 5209

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 3

davegreen100
davegreen100

Reputation: 2115

in older versions of Oracle you cannot use sequence.NEXTVAL in INSERT statements. You have to have a SELECT to put the NEXTVAL into a variable and then INSERT with the variable.

newer version of oracle allow the INSERT with sequence.NEXTVAL

Upvotes: 2

Related Questions