if (select count(column) from table) > 0 then

by

You cannot directly use a SQL statement in a PL/SQL expression:

SQL> begin
  2     if (select count(*) from dual) >= 1 then
  3        null;
  4     end if;
  5  end;
  6  /
        if (select count(*) from dual) >= 1 then
            *
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
...
...

You must use a variable instead:

SQL> set serveroutput on
SQL>
SQL> declare
  2     v_count number;
  3  begin
  4     select count(*) into v_count from dual;
  5
  6     if v_count >= 1 then
  7             dbms_output.put_line('Pass');
  8     end if;
  9  end;
 10  /
Pass

PL/SQL procedure successfully completed.

Of course, you may be able to do the whole thing in SQL:

update my_table
set x = y
where (select count(*) from other_table) >= 1;

It’s difficult to prove that something is not possible. Other than the simple test case above, you can look at the syntax diagram for the IF statement; you won’t see a SELECT statement in any of the branches.

More Related Contents:

  1. What is the replacement of cursor foreach in oracle pl/sql?
  2. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  3. Printing the value of a variable in SQL Developer
  4. PL/SQL, how to escape single quote in a string?
  5. Passing an array of data as an input parameter to an Oracle procedure
  6. Convert comma separated string to array in PL/SQL
  7. UTL_FILE.FOPEN() procedure not accepting path for directory?
  8. Oracle stored procedure with parameters for IN clause
  9. PL/SQL ORA-01422: exact fetch returns more than requested number of rows
  10. Get resultset from oracle stored procedure
  11. Split function in oracle to comma separated values with automatic sequence
  12. check if “it’s a number” function in Oracle
  13. ORA-06508: PL/SQL: could not find program unit being called
  14. How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
  15. Create Pivot view in SQL from a SQL table
  16. SQL*Plus how to accept text variable from prompt?
  17. SQL Fiddle Output Error
  18. Why doesn’t PL/SQL respect privileges granted by Roles?
  19. oracle PL/SQL how to calculate range ip for IPv6 cidr
  20. Can We use threading in PL/SQL?
  21. Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?
  22. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  23. What is the algorithm used by the ORA_HASH function?
  24. passing in table name as plsql parameter
  25. Reading clob line by line with pl\sql
  26. How to use parameters in a ‘where value in…’ clause?
  27. Why cannot I create triggers on objects owned by SYS?
  28. No more data to read from socket
  29. automating rollback script oracle
  30. How do I get column datatype in Oracle with PL-SQL with low privileges?

Leave a Comment