3

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

philipxy
  • 14,867
  • 6
  • 39
  • 83
Dinesh Manne
  • 1,824
  • 6
  • 25
  • 32

3 Answers3

6

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • I looked up the syntax for IF and CASE at http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/if_statement.htm#sthref2791 and http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/case_statement.htm#sthref2511, both of them had a boolean expression for the condition that needs to be used. – Dinesh Manne May 01 '09 at 16:12
  • Yes, that is true. I'm saying the difference isn't between IF and CASE, it's between CASE used as a procedural statement and CASE used as an expression within a SQL statement. – Dave Costa May 01 '09 at 16:28
  • Dave, I got you, I forgot the CASE usage differences in SQL and PL/SQL. So i guess using CASE in PL/SQL in the same way should fail. – Dinesh Manne May 02 '09 at 07:06
0

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;
u07ch
  • 13,324
  • 5
  • 42
  • 48
  • I guess i was not clear on what was failing, i edited the question to put that information. I was having a PL/SQL error thrown, there is no issue with dbms_output. – Dinesh Manne May 01 '09 at 10:51
  • PLS-00103 errors are normally related to syntax errors; if you put a select 'test' into the if statement does that work ?Cant get onto an oracle box today to test it myself (only have sqlserver on my laptop) – u07ch May 01 '09 at 11:22
0

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.

cagcowboy
  • 30,012
  • 11
  • 69
  • 93