Re: Oracle error for sequence

Mohammad wrote:

> Hi
>
> I'm spooling data to a file and using a sequence number generator to number
> each record and I get an ORA-02287 error. When I look up the error, it
> tells me this:
>
> ERROR at line 17:
> ORA-02287: sequence number not allowed here
>
> Here's my SQL:
>
> CREATE SEQUENCE ESEQ START WITH 1
>
>
> COLUMN FACULTY FORMAT A30
> COLUMN DEPARTMENT FORMAT A30
> COLUMN COURSE FORMAT A30
> COLUMN SECTION FORMAT A30
> COLUMN COMPONENT FORMAT A30
> COLUMN DELIVERY FORMAT A30
> COLUMN START_DATE FORMAT A30
> COLUMN END_DATE FORMAT A30
> COLUMN PATTERN FORMAT A30
> COLUMN SEQ_ORDER FORMAT A10
> COLUMN FREQUENCY_INTERVAL FORMAT A10
> COLUMN ACTIVE FORMAT A1
>
> DEFINE MY_VALUE
> COLUMN START1 NEW_VALUE MY_VALUE
> SELECT ESEQ.NEXTVAL START1 FROM DUAL;
>
> SELECT SCBCRSE_COLL_CODE FACULTY,
> SCBCRSE_DEPT_CODE DEPARTMENT,
> RPAD( SSBSECT_SUBJ_CODE, 4 )
> || ' '
> || SSBSECT_CRSE_NUMB COURSE,
> SSBSECT_SEQ_NUMB SECTION,
> SSBSECT_CRN || SSRMEET_SCHD_CODE COMPONENT,
> DECODE(SSRMEET_CATAGORY,
> NULL,'01') DELIVERY,
> TO_CHAR(SSRMEET_START_DATE, 'YYYYMMDD') START_DATE,
> TO_CHAR(SSRMEET_END_DATE, 'YYYYMMDD') END_DATE,
> SSRMEET_HRS_WEEK PATTERN,
> TO_CHAR(ESEQ.NEXTVAL) SEQ_ORDER,
> '01' FREQUENCY_INTERVAL,
> DECODE(SCBCRSE_CSTA_CODE,'A','1','0') ACTIVE
> FROM SCBCRSE, SSBSECT, SSRMEET
> WHERE SSBSECT_TERM_CODE = '&SEL_TERM_CODE'
> AND SSBSECT_SUBJ_CODE = SCBCRSE_SUBJ_CODE
> AND SSBSECT_CRSE_NUMB = SCBCRSE_CRSE_NUMB
> AND SSRMEET_CRN (+) = SSBSECT_CRN
> AND SSRMEET_TERM_CODE (+) = SSBSECT_TERM_CODE
> ORDER BY SCBCRSE_COLL_CODE,
> RPAD( SSBSECT_SUBJ_CODE, 4 )
> || ' '
> || SSBSECT_CRSE_NUMB,
> SSBSECT_SEQ_NUMB,
> SSBSECT_CRN,
> SSRMEET_CATAGORY
> /
>
> Help! Any ideas? Thanks in advance...
>
> Thanks
> Mohammad

What do you expect:
SELECT ESEQ.NEXTVAL START1 FROM DUAL;
to do?

Try

SELECT ESEQ.NEXTVAL
INTO START1
FROM DUAL; Or better yet in your SELECT statement use ESEQ.NEXTVAL as though it were a variable or column.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)