scalar subquery in if statement Condition in PL/SQL

oracle plsql conditional scalar-subquery
Dinesh Manne picture Dinesh Manne · May 1, 2009 · Viewed 12.2k times · Source

I have an If Statement block similar to the below which is failing with the error - PLS-00103: Encountered the symbol "SELECT" when expecting one of the following....

Begin
    If (select count(*) from Table1) > 0 then
        dbms_output.put_line('Test');
    end if;
end;

I have similar Case statement which works fine

select 
case 
    when (select count(*) from Table1) > 0
        then 2
    else
        1
end
from dual

From what i have read in Oracle Documentation the if and when support a Boolean Expression, any ideas whether Subqueries are supported in If Conditions.

Note: The Statements have been simplified, i am not really going to get the count of the entire table, so no optimization suggestions please

Answer

Dave Costa picture Dave Costa · May 1, 2009

No, you can't use a SELECT in the way you want.

In your example using CASE, you are not using a CASE "statement" -- you are using a CASE expression, which happens to be embedded within a SQL statement. You can use a subquery in that case because it's within the context of a SQL statement, not a procedural statement. You wouldn't be able to use a subquery like this in a procedural CASE statement.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More