Duplicate inserts but only through sqlplus

From: Anoop <anoopkumarv_at_gmail.com>
Date: 11 Apr 2007 12:17:29 -0700
Message-ID: <1176319049.037809.35830@w1g2000hsg.googlegroups.com>

Hi,

We seem to have a mysterious situation here. We need to populate data into some tables and have created a file with insert statements. The file has been run through all our dev / test and staging environments with no problems, but it fails to run on production only. On further analysis we found out that the production DBA uses good ole sqlplus to run the file using @filename syntax and on all other environments we used Oracle's SQLDeveloper (Raptor) to copy paste the inserts and run them.

Here is a sample of the first few lines in the file:

---

SET SCAN OFF
insert into applications values ('WSA','Windows Server Admin','Windows Server Admin');

/* Domains */
insert into application_data values ((select max(application_data_id) +1 from application_data), 'WSA', 'Domain', 'cof.ds.capitalone.com ', null, 0);

/*COFSvrs032607*/
insert into privilege values ((select max(privilege_id)+1 from privilege), (select max(application_data_id) from application_data),
'Server', 'A51W2KMDE01', null);

insert into privilege_detail values ((select max(detail_id)+1 from PRIVILEGE_DETAIL), (select max(privilege_id) from PRIVILEGE), 'Group',
'GR GG COF USR A51W2KMDE01 Admins', null);
insert into privilege values ((select max(privilege_id)+1 from privilege), (select max(application_data_id) from application_data),
'Server', 'ASHD3PLDA02', null);

insert into privilege_detail values ((select max(detail_id)+1 from PRIVILEGE_DETAIL), (select max(privilege_id) from PRIVILEGE), 'Group',
'GR GG COF USR ASHD3PLDA02 Admins', null);
insert into privilege values ((select max(privilege_id)+1 from privilege), (select max(application_data_id) from application_data),
'Server', 'ASHD3PLDA04', null);

---

This file when run using sqlplus using @filename inserts duplicate records for some of the data (but the same data always is duplicated). The same file copy pasted into Sql Developer runs perfectly. Can someone point us to what may be wrong - why does the script insert duplicate records when run only through Sqlplus?

I have tried removing the set scan off and many other things...

Thanks in advance,
Anoop Received on Wed Apr 11 2007 - 14:17:29 CDT