Re: EXECUTE IMMEDIATE

The another solution uses an additional variable for the sql command. If you don't want to use another variable use this:

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;
/

Lubomir Petrov

kchan_at_watercove.com (Ken) wrote in message news:<ded7f282.0110121014.15a82f16_at_posting.google.com>...
> 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 Sat Oct 13 2001 - 00:16:01 CDT