0

I have below query to use the sequence from CONNECT BY clause and insert into the table

INSERT INTO DF_SUBJECTS (SUBJECT_ID)    
SELECT 'CUST' || LPAD(your_sequence.NEXTVAL, 3, '0'), ROWNUM
FROM dual
CONNECT BY LEVEL <= (SELECT your_sequence.CURRVAL FROM dual);

Error return ORA-02287: sequence number not allowed here

Below is the definition of sequence:

 CREATE SEQUENCE your_sequence INCREMENT BY 1 MINVALUE 001 MAXVALUE 9999999999999999999999999999 NOCYCLE CACHE 20 NOORDER ;

If calling sequence is not allowed in connect by clause, any suggestion to populate the value using sequence in select query?

Improve this question
8
  • 2
    Why would you do this? If the sequence is at 5, you would generate 5 rows numbered 6,7,8,9 and 10. I fail to see the purpose. Perhaps you are generating bogus test data? You'd get the same thing by querying the table your insert these values into or just hard coding the # of rows you want. If you want to double the # of rows with bogus data, simply insert select from and back into the table so you get one new row for every existing row. There's almost never a need to consult .CurrVal Commented Oct 3, 2023 at 15:29
  • 2
    What are you trying to accomplish here? If you are ultimately trying to insert rows into a table somewhere, you'd use the sequence in the insert statement. You're not going to be able to use the sequence in a select statement because it wouldn't work the way you expect. SQL is declarative-- you don't control how the database chooses to execute the query so the sequence might get incremented many times for each row. Commented Oct 3, 2023 at 15:31
  • @PaulW, i have table to insert with the running number populated using sequence. Updated in the question Commented Oct 3, 2023 at 15:37
  • If the goal is to populate a table, you'd do that in the insert statement not in the select. Commented Oct 3, 2023 at 15:40
  • 1
    How many rows are you trying to insert, and why would that be correlated with the sequence? Commented Oct 3, 2023 at 16:00
Related questions
0
oracle multiple sequence on a single table
1394
How do I limit the number of rows returned by an Oracle query after ordering?
1
SQL command not properly ended error : Error: ORA-00933
Load 4 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.