One thing to be careful of is that if you do this frequently you are going
to have performance problems. That is if you use execute immediate in this
fashion in your code and execute the code a lot then you are forcing a lot
of hard parses.
Jim
"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
Received on Fri Oct 12 2001 - 19:30:35 CDT
