How to get Total record count of individual views from all_views

Question

How to get Total record count of individual views from all_views. Please find below code for reference..

Declare
view_name     VARCHAR2(200);
v_str          VARCHAR2 (1000);
v_output     VARCHAR2(4000);

CURSOR tbl IS
     SELECT view_name 
     FROM all_views
     WHERE OWNER = SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA')
     ORDER BY 1 ;
BEGIN
OPEN tbl ;
     LOOP
     FETCH tbl INTO view_name;
     EXIT WHEN tbl%NOTFOUND;
          v_str := 'Select '''|| view_name ||'     '' || count (*) from ' || view_name ;
          EXECUTE IMMEDIATE v_str INTO v_output;

          DBMS_OUTPUT.PUT_LINE(v_output);
     END LOOP;
CLOSE tbl;
END;

current output :

V_DSP_BUSINESS_DATE            10
V_DSP_DEPARTMENT               20
V_DSP_EMPLOYEE_DEACTIVATED     50
V_DSP_EMPLOYEE_GED             80

Expected output :

sum up the record count of all individual views

i.e 160

Kindly help.

can you make your expected output more clear i.e. you want only 160 without view name or 160 against every view name. — Dharmesh Porwal, Jan 30 '15 at 06:05
Add the count inside the loop and put the dbms_output after the end loop. See my answer. — Lalit Kumar B, Jan 30 '15 at 06:25

score 1 · Answer 1 · answered Jan 30 '15 at 06:20

Simply modify the LOOP to sum up for each time it fetches the COUNT.

LOOP
FETCH tbl INTO view_name;
EXIT WHEN tbl%NOTFOUND;
     v_str := 'Select count (*) from ' || view_name ;
     EXECUTE IMMEDIATE v_str INTO v_cnt;
     v_cnt_tot := v_cnt_tot + v_cnt;


END LOOP;
DBMS_OUTPUT.PUT_LINE(v_cnt_tot);

Make sure you declare the v_cnt and v_cnt_tot variables.

If you really want to do it in LOOP i.e. row-by-row, then use a simple CURSOR FOR LOOP rather than declaring a CURSOR.

Something like,

FOR i IN SELECT view_name 
     FROM all_views
     WHERE OWNER = SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA')
LOOP
...

You're welcome. Please mark it as answered if it has solved your problem :-) — Lalit Kumar B, Feb 02 '15 at 11:55

score 1 · Answer 2 · answered Jan 30 '15 at 07:22

thank you so much for your help...:) It worked.

Please find below final code

Declare
view_name     VARCHAR2(200);
v_str          VARCHAR2 (1000);
v_cnt     VARCHAR2(4000);
v_cnt_tot     VARCHAR2(4000);

CURSOR tbl IS
     SELECT view_name 
     FROM all_views
     WHERE OWNER = SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA')
     ORDER BY 1 ;

BEGIN
 v_cnt_tot := 0;
OPEN tbl ;
     LOOP
     FETCH tbl INTO view_name;
     EXIT WHEN tbl%NOTFOUND;
          v_str := 'Select  count (*) as count from ' || view_name ;
          EXECUTE IMMEDIATE v_str INTO v_cnt;
          v_cnt_tot := v_cnt_tot + v_cnt;


     END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_cnt_tot);
CLOSE tbl;
END;

If you are using Oracle SQL Developer, put this at the top of your script: set serveroutput on; Otherwise you will get a generic PL\SQL script ran successfully output. — Jeremy F., Aug 21 '18 at 16:24

How to get Total record count of individual views from all_views

2 Answers2