Re: EXECUTE IMMEDIATE

"Ken" <kchan_at_watercove.com> 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

Look at your code.
Into which variable are you returning the result? Currently you submit a statement for which the result gets nowhere it should read

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

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Fri Oct 12 2001 - 14:12:01 CDT