Oracle PLSQL - Subquery not allowed in this context-CodePudding

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;

CodePudding user response：

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;

CodePudding user response：

Another solution is to not use the IF-THEN-ELSE clause but wrap the select in a block use the NO_DATA_FOUND exception:

declare 
  l_ename VARCHAR2(100) := 'KING';
  l_dummy NUMBER;
begin
  BEGIN
    SELECT 1 INTO l_dummy FROM emp WHERE ENAME = l_ename AND ROWNUM = 1;
    dbms_output.put_line('found king');    
  EXCEPTION WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('king not found');    
  END;
end;
/