Re: Simple dynamic query

tunity5_at_yahoo.com wrote in
news:32bcd267.0411200820.1b136dda_at_posting.google.com:

> 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?
>

Privs acquired via roles work fat, dumb & happy from SQL*Plus. Privs acquired via roles do NOT work from within PL/SQL. It might help if you RTFM
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/toc.htm or do some independent research at http://asktom.oracle.com

HTH & YMMV Received on Sat Nov 20 2004 - 10:31:58 CST