UPGCOUNT – 2

In some cases the two preferred methods for executing UPGCOUNT do not work for you because

1. You do not have access to PSAE executable
2. You have very limited access to the environment to successfully run the AE

Here is a PL/SQL that you can use to provide similar result.

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 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_PAD||VAR_COUNT);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(CUR_REC.RECNAME||VAR_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;

/

Read More About UPGCOUNT