Function to count tab : Utility Function « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Utility Function

Function to count tab

  
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>

   
  

Related examples in the same category