Using SEQUENCES in ODBC

From: Jeffery Sumner <JeffS_at_PeerDirect.com>
Date: Fri, 06 Aug 1999 18:44:51 GMT
Message-ID: <37ab2ace.1550699296@nntp.netcom.ca>

Hi!, I'm using Oracle 7.3.3 (accessing the database via ODBC), I create a sequence as follows:

    CREATE SEQUENCE PDCFLSEQ I then try to load a table using the following INSERT statement:

    INSERT INTO "PDCACCOUNTPAYMENT"

        ("CPAYMENTNUMBER","PDCID",


         "PDCRepl","PDCDel",


         "PDCCTime",


         "PDC1","PDC2",


         "PDC1F1","PDC2F1")


    SELECT t."PAYMENTNUMBER",PDCFLSEQ.nextval,
        'R','N',
        {ts '1979-12-31 19:00:00'},
        {ts '1979-12-31 19:00:00'},0,
        {ts '1979-12-31 19:00:00'},0

    FROM "ACCOUNTPAYMENT" t

(I left the whole statement in case there's something subtly wrong in the way I'm using the sequence. Basically, I'm loading one table using the primary key of the other (plus a bunch of constants - and the sequence, of course). All columns in the above are either NUMERIC(10,0), CHAR(1) or Date; you can assume there are no type mismatches.)

This fails, giving the following error:

    S1000(4016)[INTERSOLV][ODBC Oracle driver][Oracle]     ORA-04016: sequence PDCFLSEQ no longer exists

However, the following statement then succeeds (or at least generates no error messages):

    DROP SEQUENCE PDCFLSEQ The odd thing is, for some tables, the exact same sequence of statements seems to work, but for other tables it fails. There's nothing special about any of the tables that I can see.

My question is, under what circumstances would Oracle think the sequence has gone away?

Thanks for your help!
Jeff Sumner
PeerDirect Inc
www.PeerDirect.com
Mississauga, Ontario, Canada
(jeffs_at_peerdirect.com is a spam repository. If you want to send me mail, please send to 'first initial' + 'last name' at PeerDirect dot com) Received on Fri Aug 06 1999 - 13:44:51 CDT