Simple dynamic query

I have the following simple function defined (Windows XP Pro, Oracle 9.2.0.1):

CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2) RETURN number AS tab_rows number(20);
sqlstr VARCHAR2(50);
tCursor PLS_INTEGER;

BEGIN sqlstr := 'select count(*) from ' || table_name; tCursor := dbms_sql.open_cursor;
dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE); tab_rows := dbms_sql.execute(tCursor);
dbms_sql.close_cursor(tCursor);
RETURN(tab_rows);
END tab_rows;

First, I was getting this:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32

Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus: SQL> select tab_rows('emp') from dual;
TAB_ROWS('EMP')

              0

SQL> select count(*) from emp;

  COUNT(*)

        13

So, it seems that the function tab_rows has some error somewhere but where? Received on Sat Nov 20 2004 - 10:20:42 CST