EXECUTE IMMEDIATE

I have a function defined as follows:

CREATE or replace FUNCTION zrow_count (tab_name CHAR) RETURN INT AS

    rows INT;
    BEGIN
      EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name || ' INTO rows';

      RETURN rows;
    END;
/

Function created.

But when I execute the function as follow:

select zrow_count('wcn.wcnnmsdomaintbl') from dual;

The following error occurs:

select zrow_count('wcn.wcnnmsdomaintbl') from dual

       *
ERROR at line 1:

ORA-00933: SQL command not properly ended
ORA-06512: at "WCNSECURITYMANAGER.ZROW_COUNT", line 4
ORA-06512: at line 1

Yet if I execute the command manually, it executes fine:

SQL> SELECT COUNT(*) FROM wcn.wcnnmsdomaintbl;

  COUNT(*)

         1

SQL> Any ideas?

Thanks for any help Received on Fri Oct 12 2001 - 13:14:29 CDT