Generic function utilizing dynamic SQL to return the number of rows in the specified table. : Utility Function « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Function

SQL>
SQL> CREATE OR REPLACE FUNCTION tabcount (
  2     sch IN VARCHAR2,
  3     tab IN VARCHAR2)
  4     RETURN INTEGER
  5  IS
  6     cur     INTEGER  := DBMS_SQL.OPEN_CURSOR;
  7     ignore  INTEGER;
  8     retval  INTEGER;
  9  BEGIN
 10     DBMS_SQL.PARSE (cur, 'SELECT COUNT(*) FROM ' || sch || '.' || tab, DBMS_SQL.NATIVE);
 11     DBMS_SQL.DEFINE_COLUMN (cur, 1, retval);
 12     ignore := DBMS_SQL.EXECUTE_AND_FETCH (cur);
 13     DBMS_SQL.COLUMN_VALUE (cur, 1, retval);
 14     DBMS_SQL.CLOSE_CURSOR (cur);
 15     RETURN retval;
 16  END;
 17  /

Function created.

SQL>