Oracle - dynamic SQL in Select case

Question

I'm writing a function for checking If table is empty or not, with passing table name as parameter. Here is my code :

CREATE OR REPLACE FUNCTION EMPTY_TABLE (table_name IN VARCHAR2) 
RETURN NUMBER 
IS

Is_empty NUMBER;
v_SQL VARCHAR(1000);
BEGIN

 v_SQL:= 'SELECT CASE 
           WHEN EXISTS(SELECT 1 FROM ' || table_name || ') 
           THEN  1
           ELSE 0
         END INTO Is_empty
  FROM DUAL';

 execute immediate v_SQL;

RETURN Is_empty;

END EMPTY_TABLE;
/

This code compiles without error but when I execute It, I get error : ORA-00905: missing keyword. Select Itself works fine, I'm just having problems of how to write a dynamic SQL that would concatenate table name with It. Any help appreciated !

Answer 1

INTO is at wrong place; should be part of the EXECUTE IMMEDIATE:

SQL> CREATE OR REPLACE FUNCTION EMPTY_TABLE (table_name IN VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     Is_empty   NUMBER;
  5     v_SQL      VARCHAR (1000);
  6  BEGIN
  7     v_SQL :=
  8           'SELECT CASE
  9             WHEN EXISTS(SELECT 1 FROM '
 10        || table_name
 11        || ')
 12             THEN  1
 13             ELSE 0
 14           END                               --> not here ...
 15    FROM DUAL';
 16
 17     EXECUTE IMMEDIATE v_SQL INTO Is_empty;  --> ... but here
 18
 19     RETURN Is_empty;
 20  END EMPTY_TABLE;
 21  /

Function created.

SQL> SELECT empty_table ('dept') FROM DUAL;

EMPTY_TABLE('DEPT')
-------------------
                  1

SQL> SELECT empty_table ('dept') FROM DUAL;