CODEWITHSUNDEEP
oracle-databaseplsqlconditional-statementsscalar-subquery
Dinesh Manne
Dinesh Manne

Reputation: 1932

scalar subquery in if statement Condition in PL/SQL

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

Upvotes: 3

Views: 12491

Answers (3)

Dave Costa
Dave Costa

Reputation: 48111

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.

Upvotes: 6

cagcowboy
cagcowboy

Reputation: 30828

I don't believe subqueries are supported in IF conditions... PL/SQL will be expecting the SELECT to give it a set of records, not a single value to be used in a expression/statement.

Upvotes: 0

u07ch
u07ch

Reputation: 13692

Have you called DBMS_OUTPUT.ENABLE

Quick example

BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.PUT_LINE('Disabled');
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('Enabled');
END;

Upvotes: 0

Related Questions