UpgCount

set serverout on size 1000000
DECLARE
CURSOR C1 IS
SELECT RECNAME, SQLTABLENAME FROM PSRECDEFN WHERE RECTYPE = 0 ORDER BY RECNAME;

VAR_COUNT NUMBER;
VAR_REC_COUNT NUMBER :=0;
VAR_SQLTEXT VARCHAR2(32000);
VAR_RECNAME VARCHAR2(64);
VAR_PAD VARCHAR2(100);
VAR_DATE DATE;

BEGIN

SELECT SYSDATE
INTO VAR_DATE
FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Report for Record Rowcount');
DBMS_OUTPUT.PUT_LINE('START DATE AND TIME:'TO_CHAR(VAR_DATE,'MM/DD/YYYY HH24:MI:SS'));

DBMS_OUTPUT.PUT_LINE('RECNAME Row Count');
DBMS_OUTPUT.PUT_LINE('============================== =========');

FOR CUR_REC IN C1
LOOP


--VAR_PAD := RPAD(' ',31 - LENGTH(CUR_REC.RECNAME));
IF SUBSTR(CUR_REC.SQLTABLENAME,1,2) = 'PS' THEN
VAR_RECNAME := CUR_REC.RECNAME;
ELSE
VAR_RECNAME := 'PS_'CUR_REC.RECNAME;
END IF;

--IF NVL(LENGTH(RTRIM(CUR_REC.SQLTABLENAME)),0) = 0 THEN
-- VAR_RECNAME := 'PS_'CUR_REC.RECNAME;
--END IF;

VAR_REC_COUNT := VAR_REC_COUNT + 1;

BEGIN

VAR_SQLTEXT := 'SELECT COUNT(*) FROM 'VAR_RECNAME;
EXECUTE IMMEDIATE VAR_SQLTEXT INTO VAR_COUNT;

DBMS_OUTPUT.PUT_LINE(CUR_REC.RECNAME' 'VAR_PADVAR_COUNT);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(CUR_REC.RECNAMEVAR_PAD'ERROR - 'SQLERRM);

END;
END LOOP;

DBMS_OUTPUT.PUT_LINE('============================== =========');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Total Number of Tables = 'VAR_REC_COUNT);

SELECT SYSDATE
INTO VAR_DATE
FROM DUAL;

DBMS_OUTPUT.PUT_LINE('END DATE AND TIME:'TO_CHAR(VAR_DATE,'MM/DD/YYYY HH24:MI:SS'));
END;