On Tuesday, September 11, 2012 12:47:53 PM UTC-6, imalukegal wrote:
>
> I have to join several tables together from database #1 and insert the
> information to one table in database #2. The problem is that the extracted
> data may contain records that are considered duplicate records on the
> database #2 table. We are using sql to do the inserts.
> We cannot write the contents from the first database to a file, delete the
> dups, and then upload to database #2 because the job will run forever
> (another developer already tried this approach). We are not allowed to
> eliminate the unique constraint on database #2 table. When the duplicate
> record is encountered, processing stops, and the oncall programmer is
> contacted.
>
> Ideally, I would like to write the duplicate record to an error file and
> continue processing. Is there a way to do this?
>
> Thank you for any suggestions.
>
>
>
You don't mention which release of Oracle you're using; ideally Mike's
suggestion is the best (use the merge into command):
SQL> create table empm as select * from emp where deptno = 10;
Table created.
SQL>
SQL> merge into empm m
2 using emp e
3 on ( e.EMPNO = m.EMPNO )
4 when not matched then
5 insert (m.empno, m.ename, m.job)
6 values (e.empno, e.ename, e.job)
7 where e.deptno=20;
5 rows merged.
SQL>
SQL> select * From empm;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7839 KING PRESIDENT 17-NOV-81 5000
10
7934 MILLER CLERK 7782 23-JAN-82 1300
10
7788 SCOTT ANALYST
7566 JONES MANAGER
7902 FORD ANALYST
7369 SMITH CLERK
7876 ADAMS CLERK
8 rows selected.
SQL>
Of course the duplicate records are not written anywhere in that example;
you can also use a little-used option to insert, the log errors clause.
Below is an example of how to use it:
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
SQL>
SQL> exec dbms_errlog.create_error_log('emp','errlog');
PL/SQL procedure successfully completed.
SQL>
SQL> desc errlog
Name
Null? Type
------------------------------------------------------------------------
-------- ----------------------------
ORA_ERR_NUMBER$
NUMBER
ORA_ERR_MESG$
VARCHAR2(2000)
ORA_ERR_ROWID$
ROWID
ORA_ERR_OPTYP$
VARCHAR2(2)
ORA_ERR_TAG$
VARCHAR2(2000)
EMPNO
VARCHAR2(4000)
ENAME
VARCHAR2(4000)
JOB
VARCHAR2(4000)
MGR
VARCHAR2(4000)
HIREDATE
VARCHAR2(4000)
SAL
VARCHAR2(4000)
COMM
VARCHAR2(4000)
DEPTNO
VARCHAR2(4000)
SQL>
SQL> select empno, ename, sal, mgr from emp;
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7369 SMITH 800 7902
7499 ALLEN 1600 7698
7521 WARD 1250 7698
7566 JONES 2975 7839
7654 MARTIN 1250 7698
7698 BLAKE 2850 7839
7782 CLARK 2450 7839
7788 SCOTT 3000 7566
7839 KING 5000
7844 TURNER 1500 7698
7876 ADAMS 1100 7788
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7900 JAMES 950 7698
7902 FORD 3000 7566
7934 MILLER 1300 7782
14 rows selected.
SQL>
SQL> insert into emp
2 select * from emp union select 8001, 'PEABO', 'JANITOR', 7782,
sysdate-20, 975, 0, 30 from dual
3 log errors into errlog('whoops') reject limit 15;
1 row created.
SQL>
SQL> select empno, ename, sal, mgr from emp;
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7369 SMITH 800 7902
7499 ALLEN 1600 7698
7521 WARD 1250 7698
7566 JONES 2975 7839
7654 MARTIN 1250 7698
7698 BLAKE 2850 7839
7782 CLARK 2450 7839
7788 SCOTT 3000 7566
7839 KING 5000
7844 TURNER 1500 7698
7876 ADAMS 1100 7788
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7900 JAMES 950 7698
7902 FORD 3000 7566
7934 MILLER 1300 7782
8001 PEABO 975 7782
15 rows selected.
SQL>
SQL> column ora_err_mesg$ format a60
SQL> column ora_err_tag$ format a10
SQL> column empno format a6
SQL> column ename format a15
SQL> column sal format a15
SQL>
SQL> select ora_err_mesg$, ora_err_tag$, empno, ename, sal from errlog;
ORA_ERR_MESG$ ORA_ERR_TA
EMPNO ENAME SAL
------------------------------------------------------------ ----------
------ --------------- ---------------
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7369 SMITH 800
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7499 ALLEN 1600
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7521 WARD 1250
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7566 JONES 2975
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7654 MARTIN 1250
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7698 BLAKE 2850
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7782 CLARK 2450
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7788 SCOTT 3000
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7839 KING 5000
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7844 TURNER 1500
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7876 ADAMS 1100
ORA_ERR_MESG$ ORA_ERR_TA
EMPNO ENAME SAL
------------------------------------------------------------ ----------
------ --------------- ---------------
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7900 JAMES 950
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7902 FORD 3000
ORA-00001: unique constraint (BING.EMP_PK) violated whoops
7934 MILLER 1300
14 rows selected.
SQL>
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
