Using an oracle sequence directly in the CONNECT BY clause

71 views Asked by At

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?

0

There are 0 answers