Re: EXECUTE IMMEDIATE

From: Lubomir Petrov <lpetrov_at_yahoo.com>
Date: 12 Oct 2001 20:12:51 -0700
Message-ID: <75805ad9.0110121912.c3cf1d4@posting.google.com>

Try it this way:

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

    rows INT;
    SQLCommand varchar2(200);
    BEGIN

      SQLCommand := 'SELECT COUNT(*) FROM ' || tab_name;
      EXECUTE IMMEDIATE SQLCommand INTO rows;
      RETURN rows;

    END; INTO clause for 'EXECUTE STATEMENT' must not be in the SQL statement.

HTH 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 Fri Oct 12 2001 - 22:12:51 CDT