| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Duplicate inserts but only through sqlplus
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');
SQL> SQL> /* Domains */ SQL> insert into application_data values ((selectmax(application_data_id)+1 from application_data), 'WSA', 'Domain', 'cof.ds.xxx.com ', null, 0);
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
Received on Wed Apr 11 2007 - 15:06:39 CDT
 |
 |