CODEWITHSUNDEEP
plsqloracle11g
user3782821
user3782821

Reputation: 29

PL/SQL insert based on conditions

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_transaction
from entry e, view v
where e.owner = v.owner
and e.transaction = v.transaction;

EXCEPTION
when too_many_rows
then
  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: enter image description here

Upvotes: 1

Views: 2621

Answers (3)

StewS2
StewS2

Reputation: 421

I think Dmitry was trying to suggest using MERGE in the too_many_rows case of your exception handler. So you've already done the SELECT up front and determined that the Entry row appears in your View table and so it raises the exception too_many_rows.

The problem is that you don't know which records have thrown the exception (assuming your Entry table has more than one row easy time this procedure is called). So I think your idea of using the exception section to determine that you have too many rows was elegant, but insufficient for your needs.

As a journeyman programmer, instead of trying to come up with something terribly elegant, I'd use more brute force.

Something more like:

BEGIN
    FOR entry_cur IN
        (select e.owner, e.transaction, SUM(NVL2(v.owner, 1, 0)) rec_count
           from entry e, view v
          where e.owner = v.owner(+)
            and e.transaction = v.transaction(+)
         GROUP BY e.owner, e.transaction)
    LOOP
        CASE WHEN rec_count > 0
             THEN INSERT INTO view
             ELSE MERGE INTO rejected r
                     ON (r.transaction = entry_cur.transaction
                    AND r.owner = entry_cur.owner)
                   WHEN NOT MATCHED THEN INSERT blah blah blah
        ;
    END LOOP;
END;

The HAVING COUNT(*) > 1 will No exceptions thrown. The loop gives you the correct record you don't want to insert into View. BTW, I couldn't get over that you used keywords for object names - view, transaction, etc. You enquoted the table names on the CREATE TABLE "VIEW" statement, which gets around the fact that these are keywords, but you didn't when you referenced them later, so I'm surprised the compiler didn't reject the code. I think that's a recipe for disaster because it makes debugging so much harder. I"m just hoping that you did this for the example here, not in PL/SQL.

Personally, I've had trouble using the MERGE statement where it didn't seem to work consistently, but that was an Oracle version long ago and probably my own ignorance in how it should work.

Upvotes: 2

TommCatt
TommCatt

Reputation: 5636

You're trying to code yourself out of a quandary you've modeled yourself into.

A table should contain your entity. There should not be a table of entities in one state, another table for entities in another state, and yet another table for entities in a different state altogether. You're seeing the kind of problems this can lead to.

The state can be an attribute (field or column) of the one table. Or normalized to a state table but still only one entity table. When an entity changes states, this is accomplished by an update, not a move from one table to another.

Upvotes: 1

Dmitriy
Dmitriy

Reputation: 5565

Use MERGE statement:

merge into REJECTED r
using ENTRY e
   on (r.type = e.type and
       r.transaction = e.transaction and
       r.owner = e.owner)
 when not matched then insert (type, transaction, owner)
 values (e.type, e.transaction, e.owner)

This query will insert into table REJECTED only combinations of (type, transaction, owner) from table ENTRY that are not present there yet.

Upvotes: 1

Related Questions