SQL - Spooling inserts

Question

I am creating insert statements to populate my tables. They run fine when I test them, however, when I start to spool the same code that worked previously suddenly gives me an error (ORA-00001: unique constraint violated). When researching the issue it seems that the issue occurs when trying to enter duplicate information into a table. However, I always clear the sequences and re-enter them before spooling so this wouldn't make sense. Here is an example of what I'm trying to run, and as I said the code works fine otherwise:

SQL> INSERT INTO bill_tos_sr
2 (
3 bill_to_no,
4 bill_to_name,
5 bill_to_street,
6 bill_to_city,
7 bill_to_state,
8 bill_to_zip,
9 bill_to_phone  
10 )
11 VALUES
12 (bill_tos_seq.NEXTVAL,
13 'Walmart',
14 '1000 Indiantown St',
15 'Ft Lauderdale',
16 'FL',
17 '33401',
18 '9438476698'
19 );
INSERT INTO bill_tos_sr
*
ERROR at line 1:
ORA-00001: unique constraint (MONPM16.BILL_TO_NO_PK) violated

Answer 1

I'll assume BILL_TO_NO_PK is the PK/unique constraint of the column BILL_TO_NO.

When inserting, you are are providing new values from the sequence bill_tos_seq.NEXTVAL. It seems the sequence was restarted, or that data was entered manually in the table.

In any case, the sequence values are colliding with existing data in the table.

Solution? I would find the max value for the column, and then set the sequence value to one more than that value.

Answer 2

Resetting the sequence (dropping and recreating in this case) has no effect on data that already exists in the table. There is no real relationship between the sequence and table, except through your code. (You can use the same sequence across multiple tables, for instance). That wouldn't be true if you were using an identity column in 12c+; but you aren't in your example.

So really what you have done is:

create sequence bill_tos_seq;
insert into bill_tos_sr (bill_to_no, ...) values (bill_tos_seq.nextval, ...);
drop sequence bill_tos_seq;
create sequence bill_tos_seq;
insert into bill_tos_sr (bill_to_no, ...) values (bill_tos_seq.nextval, ...);

Unless you specified otherwise, both versions of the sequence will start from 1, which means both of your inserts will try to create a row with bill_to_no set to 1 - which violates the constraint.

You need to delete the data from the table before you try to reinsert it. If it was empty to start with then you can do a simple truncate or delete (being careful, of course!).

create sequence bill_tos_seq;
insert into bill_tos_sr (bill_to_no, ...) values (bill_tos_seq.nextval, ...);
drop sequence bill_tos_seq;
truncate table bill_tos_sr;
create sequence bill_tos_seq;
insert into bill_tos_sr (bill_to_no, ...) values (bill_tos_seq.nextval, ...);

If it already had some data before you started then you'll need to figure out which rows you inserted the first time and only delete those.

You could also have tested and re-run this with a rollback:

create sequence bill_tos_seq;
insert into bill_tos_sr (bill_to_no, ...) values (bill_tos_seq.nextval, ...);
rollback;
drop sequence bill_tos_seq;
create sequence bill_tos_seq;
insert into bill_tos_sr (bill_to_no, ...) values (bill_tos_seq.nextval, ...);

Note that if you did that it would have to be before the drop/create of the sequence, because DDL commits implicitly; so a rollback afterwards would have no effect.

It's possible you rolled back at the wrong point, or that you didn't roll back at all and didn't realise the data had been committed for some other reason - perhaps you exited SQL*Plus and didn't realise that would commit by default too. Once the data has been committed you have to delete it before you can reinsert it, or you'll continue to get constraint violations.

However, this has nothing to do with spooling the output.