Oracle PLS-00103 error. How do you check for an existing record and do update or insert based on that condition?

Question

I need to check if a record exists in the table or not from a SELECT statement. If the record exists, do an update otherwise create a record on the table. I'm trying to but i'm getting PLS-00103 error.

These are the errors that I'm getting when i run my code in DBVisaulzier:

18:00:09  [DECLARE - 0 row(s), 0.000 secs]  [Error Code: 6550, SQL State: 65000]  ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

:= . ( @ % ; not null range default character
18:00:09  [BEGIN - 0 row(s), 0.000 secs]  [Error Code: 6550, SQL State: 65000]  
ORA-06550: line 2, column 97:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;
18:00:09  [IF - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
18:00:09  [ELSE - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]   
ORA-00900: invalid SQL statement
18:00:09  [END - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
18:00:09  [END - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
... 6 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 6 errors]

The following is my code:

DECLARE a NUMBER;

BEGIN

  SELECT 1 
    INTO a 
    FROM FREC_EMAIL t
   WHERE t.FranchiseNo = '208254846' 
     AND t.ReportID = 1 
     AND t.id = 165;

  IF a=1 THEN

       UPDATE FREC_EMAIL
          SET email = '[email protected]'
        WHERE FranchiseNo = '208254846' 
          AND ReportID = 1 
          AND ID = 165;

    ELSE

        INSERT INTO FREC_EMAIL
          (FranchiseNo, Email, ReportID)
         VALUES
           ('208254846', '[email protected]', 1);

    END IF;
END;

Answer 1

In a pl/sql block, you can do this:

update table set column=.... where.....;

if SQL%ROWCOUNT = 0 THEN insert...... END IF;

K

Answer 2

Use the MERGE command (also called upsert by some). Oracle's reference (with example) here.

Answer 3

We should always use SQL whenever possible, and avoid using Pl/SQL unless it is strictly necessary. SQL statements perform faster, they usually require less typing and they are easier to get right.

Since 9i Oracle has provided MERGE, a single SQL statement which executes an "upsert" statement.

MERGE INTO frec_email t
USING (SELECT  '[email protected]' as email
                ,  '208254846' as FranchiseNo
                , 1 as ReportID
                , 165 as ID 
       FROM dual ) s
ON (s.ID = t.ID)
WHEN MATCHED THEN
     UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN
    INSERT (t.FranchiseNo, t.Email, t.ReportID)
    VALUES  (s.FranchiseNo, s.Email, s.ReportID)
/

Answer 4

On a side note, if you are new to Oracle, it is worth spending time getting to grips with the offical documentation. Although it might appear difficult where to start with it, Tom Kyte's Road Map is good place to get a list of must reads.

Good luck!

Answer 5

create or replace trigger audittrigger after insert or update or delete on audit1 for each row begin if inserting then insert into audit1 values('parent','insertrecord','dd.parentid',:new.parentid,sysdate); else updating then insert into audit1 values('parent','update',:old.parentid,:new.parentid,sysdate); elsif deleting then insert into audit1 values('parent','delete',:old.parentid,:new.parentid,sysdate); end elsif; end;

pls i wont solution