Oracle PLSQL – Subquery not allowed in this context

September 30, 2022

How fix this error?

Error(18857,56): PLS-00405: subquery not allowed in this context

if (V_MY_ID in (Select my_id from my_table where id = p_id)) then
begin
     if (V_IS_AVALABLE = 0) then
        Update ...........
     else
        insert into ...................
     end if;      
end;
end if;

>Solution :

Take that subquery out of IF-THEN-ELSE and see whether there are any rows that satisfy the condition; if so, do something; else, do something else. For example:

declare
  l_cnt number;
begin
  -- this is your subquery
  select count(*)
    into l_cnt
    from my_table
    where id = p_id
      and my_id = v_my_id;

  -- this is slightly modified IF-THEN-ELSE
  if l_cnt > 0 THEN
     begin
       if v_is_available = 0 then
          update ...
       else
          insert into ...
       end if;
     end;
  end if;
end;