3

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 = 'blah@foo.com'
        WHERE FranchiseNo = '208254846' 
          AND ReportID = 1 
          AND ID = 165;

    ELSE

        INSERT INTO FREC_EMAIL
          (FranchiseNo, Email, ReportID)
         VALUES
           ('208254846', 'blah@foo.com', 1);

    END IF;
END;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jack
  • 9,843
  • 23
  • 78
  • 111
  • 1
    Consider using the MERGE statement. – Alohci Sep 22 '09 at 23:19
  • Can you give me an example? Sorry for asking but I'm a newbie when it come to PL/SQL. – Jack Sep 22 '09 at 23:25
  • Looks like the compiler is complaining about a couple of things. Could you try changing SELECT 1 INTO a to SELECT ID INTO a? – David Andres Sep 22 '09 at 23:39
  • The ORA errors point to an attempt to run an invalid SQL statement. There's nothing wrong with "SELECT 1 INTO a..." - I believe the issue is due to Jack attempting to run this as an anonymous sproc. Could be a matter of a missing "/" at the end for SQLPlus... – OMG Ponies Sep 23 '09 at 01:27

5 Answers5

9

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  'blah@foo.com' 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)
/
APC
  • 144,005
  • 19
  • 170
  • 281
  • Thank you for the example but when i execute the above in DbVisualizer. It gave me 10:14:13 [MERGE - 0 row(s), 0.000 secs] [Error Code: 905, SQL State: 42000] ORA-00905: missing keyword ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] I think the statement are correct. Do you know why it is error out on missing keyword? – Jack Sep 23 '09 at 15:19
  • Jack - it was a syntax error: it is NOT MATCHED rather than UMATCHED. Serves me right for not creating a table to test the code against. Apologies. – APC Sep 23 '09 at 15:25
  • APC, how do you test on another condition beside the ID? The ID is Email table id but I also want to test ReportID which is tie to another table named Reports. The update work flawless but the Not matched clause to create the record doesn't seem to work if the ID is empty. I'm using this sql block in Oracle Apex to do update or insert an record. – Jack Sep 23 '09 at 15:52
  • Thanks for providing an example, didn't know it was a 9i feature. – OMG Ponies Sep 23 '09 at 15:52
  • APC, Nevermind. I figure it out. I will pass in 0 for the ID if there is no Email ID. – Jack Sep 23 '09 at 15:56
0

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

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

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

K

Karl T.
  • 492
  • 3
  • 3
  • Hi thank you for your help. I don't think i understand the SQL%ROWCOUNT=0 part. Can you give me a simple example? Please excuse a sql newbie. – Jack Sep 22 '09 at 23:21
  • @Jack: SQL%ROWCOUNT is an indicator of how many rows were affected by the previous statement. The previous statement in this example is an update statement. The logic here is that if zero records are affected by an update to the record then the record itself must not exist. Under these circumstances, it is safe to insert the record. – David Andres Sep 22 '09 at 23:28
0

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

Pop
  • 3,932
  • 1
  • 17
  • 12
0

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!

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
0

I usually use the following concept, which I think is more readable than merge::

BEGIN

  UPDATE FREC_EMAIL
     SET email = 'blah@foo.com'
   WHERE FranchiseNo = '208254846' 
     AND ReportID = 1 
     AND ID = 165;

  IF SQL%NOTFOUND THEN
    INSERT INTO FREC_EMAIL
          (FranchiseNo, Email, ReportID, ID)
      VALUES
          ('208254846', 'blah@foo.com', 1, 165);
  END IF;

END;
torzech
  • 404
  • 5
  • 8