SQL> CREATE OR REPLACE FUNCTION tabcount (tab IN VARCHAR2, whr IN VARCHAR2 := NULL)
2 RETURN PLS_INTEGER AUTHID CURRENT_USER
3 IS
4 str VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || tab;
5 retval PLS_INTEGER;
6 BEGIN
7 IF whr IS NOT NULL
8 THEN
9 str := str || ' WHERE ' || whr;
10 END IF;
11
12 EXECUTE IMMEDIATE str
13 INTO retval;
14 EXCEPTION
15 WHEN OTHERS
16 THEN
17 DBMS_OUTPUT.put_line ('TABCOUNT ERROR: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
18 DBMS_OUTPUT.put_line (str);
19 RETURN NULL;
20 END;
21 /
Function created.
SQL>
|