Re: Duplicate inserts but only through sqlplus

On Apr 11, 4:06 pm, "Anoop" <anoopkum..._at_gmail.com> wrote:
> On Apr 11, 3:25 pm, "EdStevens" <quetico_..._at_yahoo.com> wrote:
>
>
>
> > On Apr 11, 2:17 pm, "Anoop" <anoopkum..._at_gmail.com> wrote:
>
> > > 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.xxx.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
>
> > How do you define 'duplicated data'?
> > Show table defs.
> > Show run-time output of your sqlplus session, with ECHO ON FEEDBACK ON
> > VERIFY ON.
>
> Here is the output with ECHO ON, FeedBACK ON and VERIFY ON (It was no
> different than with just echo on):
>
> SQL> set echo on
> SQL> set feedback on
> SQL> set verify on
> SQL> @c:/WSA_Domains_AM_april9.sql
> SQL> SET SCAN OFF
> SQL>
> SQL> insert into applications values ('WSA','Windows Server
> Admin','Windows Server Admin');
> 1 row created.
> SQL>
> SQL> /* Domains */
> SQL> insert into application_data values ((select
> max(application_data_id)+1 from application_data), 'WSA', 'Domain',
> 'cof.ds.xxx.com ', null, 0);
> 1 row created.
> SQL>
> SQL> /*COFSvrs032607*/
> 1 row created.
> SQL> insert into privilege values ((select max(privilege_id)+1 from
> privilege), (select max(application_data_id) from application_data),
> 'Server', 'A51W2KMDE01', null);
> 1 row created.
> SQL> 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);
> 1 row created.
> SQL> insert into privilege values ((select max(privilege_id)+1 from
> privilege), (select max(application_data_id) from application_data),
> 'Server', 'ASHD3PLDA02', null);
> 1 row created.
> SQL> 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);
> 1 row created.
> SQL> insert into privilege values ((select max(privilege_id)+1 from
> privilege), (select max(application_data_id) from application_data),
> 'Server', 'ASHD3PLDA04', null);
> 1 row created.
> SQL> insert into privilege_detail values ((select max(detail_id)+1
> from PRIVILEGE_DETAIL), (select max(privilege_id) from PRIVILEGE),
> 'Group', 'GR GG COF USR ASHD3PLDA04 Admins', null);
> 1 row created.
> SQL> insert into privilege values ((select max(privilege_id)+1 from
> privilege), (select max(application_data_id) from application_data),
> 'Server', 'ASHP3PAST01', null);
> 1 row created.
>
> By Duplicates I mean that the data in some rows repeat in this step:
> insert into application_data values ((select max(application_data_id)
> +1 from application_data), 'WSA', 'Domain', 'cof.ds.xxx.com ', null,
> 0);
>
> So basically we find (after running the file) that cof.ds.xxx.com
> repeated twice instead of once. The file has only one row with the cof
> value - I am sure of that.
>
> We have about 3000 insert statements in all. By putting commit
> statements in between we seem to reduce the number of duplicates, but
> I fail to see how that would help. Is it really necessary to add a
> commit after a specified number of inserts??
>
> Thanks,
> Anoop

never mind all - I figured out the solution. It was the comments that I had included in the file. Once I removed the comments (/* XXX */), it works perfectly, no duplicates at all.

But still - beats me: Why would sqlplus insert duplicates if there are comments like /* XXX */. The normal expected reaction (IMO) would be to throw an error and stop or just ignore and carry on - not go out of the way to remember some old inserts and redo the inserts!!!

Anyways - this may be one place where commenting the code is not a good idea.

Thanks to all who responded and those who considered helping.

Anoop Received on Wed Apr 11 2007 - 16:06:42 CDT