CODEWITHSUNDEEP
oracle-databaseoracle11g
Adarsh Ravi
Adarsh Ravi

Reputation: 955

Getting Unknown Command error on IF-THEN-ELSE

I have the following query that I am using in Oracle 11g

IF EXISTS (SELECT * FROM EMPLOYEE_MASTER WHERE EMPID='ABCD32643') 
THEN 
update EMPLOYEE_MASTER set EMPID='A62352',EMPNAME='JOHN DOE',EMPTYPE='1' where EMPID='ABCD32643' ;
ELSE 
insert into EMPLOYEE_MASTER(EMPID,EMPNAME,EMPTYPE) values('A62352','JOHN DOE','1') ;
END IF;

On running the statement I get the following output:

Error starting at line : 4 in command -
ELSE 
Error report -
Unknown Command


1 row inserted.

Error starting at line : 6 in command -
END IF
Error report -
Unknown Command

The values get inserted with error when I run it directly. But when I try to execute this query through my application I get an oracle exception because of the error generated :

ORA-00900: invalid SQL statement

And hence the values are not inserted.

I am relatively new to Oracle. Please advise on what's wrong with the above query so that I could run this query error free.

Upvotes: 1

Views: 2676

Answers (2)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

If MERGE doesn't work for you, try the following:

begin
  update EMPLOYEE_MASTER set EMPID='A62352',EMPNAME='JOHN DOE',EMPTYPE='1' 
    where EMPID='ABCD32643' ;
  if SQL%ROWCOUNT=0 then
    insert into EMPLOYEE_MASTER(EMPID,EMPNAME,EMPTYPE)
      values('A62352','JOHN DOE','1') ;
  end if;
end;

Here you you the update on spec, then check whether or not you found a matching row, and insert in case you didn't.

Upvotes: 2

APC
APC

Reputation: 146309

"what's wrong with the above query "

What's wrong with the query is that it is not a query (SQL). It should be a program snippet (PL/SQL) but it isn't written as PL/SQL block, framed by BEGIN and END; keywords.

But turning it into an anonymous PL/SQL block won't help. Oracle PL/SQL does not support IF EXISTS (select ... syntax.

Fortunately Oracle SQL does support MERGE statement which does the same thing as your code, with less typing. 

merge into EMPLOYEE_MASTER em
using ( select 'A62352' as empid,
               'JOHN DOE' as empname,
               '1' as emptype
         from dual ) q
on (q.empid = em.empid)
when not matched then
    insert (EMPID,EMPNAME,EMPTYPE) 
           values (q.empid, q.empname, q.emptype)
when matched then
    update 
    set em.empname = q.empname, em.emptype = q.emptype
/

Except that you're trying to update empid as well. That's not supported in MERGE. Why would you want to change the primary key?

"Does this query need me to add values to all columns in the table? "

The INSERT can have all the columns in the table. The UPDATE cannot change the columns used in the ON clause (usually the primary key) because that's a limitation of the way MERGE works. I think it's the same key preservation mechanism we see when updating views. Find out more.

Upvotes: 0

Related Questions