I would appreciate all help I can get. I'm learning PL/SQL and have stumbled on a problem so please help me find an appropriate way of handling this situation I'm running Oracle 11gR2
My schema:
CREATE TABLE "ENTRY" (
"TYPE" VARCHAR2(5 CHAR) ,
"TRANSACTION" VARCHAR2(5 CHAR),
"OWNER" VARCHAR2(5 CHAR)
);
CREATE TABLE "VIEW"
(
"TYPE" VARCHAR2(5 CHAR) ,
"TRANSACTION" VARCHAR2(5 CHAR),
"OWNER" VARCHAR2(5 CHAR)
);
CREATE TABLE "REJECTED"
(
"TYPE" VARCHAR2(5 CHAR) ,
"TRANSACTION" VARCHAR2(5 CHAR),
"OWNER" VARCHAR2(5 CHAR)
);
My sample data:
insert into entry (type, transaction, owner) values (11111, 11111, 11111); `insert into entry (type, transaction, owner) values (22222, 22222, 22222);`
Now for the puzzling part, I've wrote this procedure that should copy the values from the ENTRY table to VIEW table if a record does not exist for specific (transaction AND owner) combination. If such a combination exists in the VIEW table that record should then go to the REJECTED table. This procedure does that but on multiple runs of the procedure I get more and more entries in the REJECTED table so my question is how to limit inserts in the REJECTED table - if a record already exists in the REJECTED table then do nothing.
create or replace PROCEDURE COPY AS v_owner_entry ENTRY.owner%TYPE; v_transaction_entry ENTRY.transaction%TYPE; v_owner VIEW.owner%TYPE;v_transaction VIEW.transaction%TYPE;
begin
begin select e.owner, e.transaction, v.owner, v.transaction into v_owner_entry, v_transaction_entry, v_owner, v_transactionfrom entry e, view vwhere e.owner = v.owner and e.transaction = v.transaction;
EXCEPTION when too_many_rowsthen insert into REJECTED ( TYPE, TRANSACTION, OWNER ) SELECT s1.TYPE, s1.TRANSACTION, s1.OWNER FROM ENTRY s1;
when no_data_found THEN insert into VIEW ( TYPE, TRANSACTION, OWNER ) SELECT s.TYPE, s.TRANSACTION, s.OWNER FROM ENTRY s; end; end;
Any suggestions guys?
Cheers!
UPDATE
Sorry if the original post wasn't clear enough - The procedure should replicate data (on a daily basis) from DB1 to DB2 and insert into VIEW or REJECTED depending on the conditions. Here is a photo, maybe it would be clearer:
