Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get Total record count of individual views from all_views

588fde8e-dc3f-4cde-9c97-ef0f3ab5ed99Jan 30 2015 — edited Jan 30 2015

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


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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 27 2015
Added on Jan 30 2015
2 comments
1,410 views