Re: Oracle error for sequence

"Mohammad" <mhakimjavadi_at_hotmail.com> wrote in message news:cc59b627.0305151150.11c35b3_at_posting.google.com...
> 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

Whatever the cause of the error is, you seem to use a sequence to mimic the ROWNUM pseudo function. That doesn't make any sense, and you will be wasting sequence numbers.
However for the ROWNUM function to work with ORDER BY you need to take special precautions, which are outlined in your docs. I can't be any more specific as I am not clairvoyant with respect to the version you are using.

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address