In some cases the two preferred methods for executing UPGCOUNT do not work for you because
- You do not have access to PSAE executable
- 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
