Table Count function : 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     tab IN VARCHAR2,
  3     whr IN VARCHAR2 := NULL,
  4     sch IN VARCHAR2 := NULL)
  5     RETURN INTEGER
  6  IS
  7     retval INTEGER;
  8  BEGIN
  9     EXECUTE IMMEDIATE
 10        'SELECT COUNT(*)
 11           FROM ' || NVL (sch, USER) || '.' || tab ||
 12        ' WHERE ' || NVL (whr, '1=1')
 13        INTO retval;
 14     RETURN retval;
 15  END;
 16  /

Function created.

SQL>
SQL>
SQL>