Re: 'Simple' Function or Procedure w/SELECT

In article <sreiss.24.2DEBC7F2_at_wpo.borland.com> sreiss_at_wpo.borland.com (Steve Reiss) writes:
>
> All,
>
> I'm trying to write a very simple (I hope) function or procedure that when
> passed a table name or search criteria, it will return a value such as the
> number of records in the table, or the entire row. This would be the result
> of a SELECT statement. Or put another way, a function that returns the all
> the rows that meet a condition.
>
> I've tried w/o success to create a CountRec function. I get a number of
> errors and can't proceed:
>
>
> CREATE FUNCTION CountRec(tblName VARCHAR2) RETURN NUMBER
> IS CURSOR CNT_CURSOR IS
>
> SELECT COUNT(*) FROM tblName;
>

You can not do this. You can only use bind-variables (or PL/SQL variables) in place of column-names, expressions or constants in the select-list, where clause, order by, group by, etc, etc. You can not have a variable table name.

In Oracle v7.1 we will be supplying a dynamic-SQL package enabling you to use something resembling dynamic SQL method 4 in Pro*C from within PL/SQL. You can get rowcount of all of your tables with SQLPLUS:

set termout off feedback off pagesize 0
spool count.sql
select 'select ''' || table_name || ''', count(1) from ' ||

        table_name || ';'
from user_tables;
spool off
_at_count

Hope this helps...

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_se.oracle.com