ACS3 Data Model

     1: procedure ad_verify_views_by_select
     2: IS
     3:   v_view_name   varchar(50);
     4:   v_sql		varchar(4000);  -- for dynamic sql
     5: 
     6:   cursor c_user_views IS
     7:     select view_name from user_views;
     8: 
     9: BEGIN
    10: 
    11: 	open c_user_views;
    12: 
    13: 	LOOP
    14: 	    	fetch c_user_views into v_view_name;
    15: 	    	exit when c_user_views%NOTFOUND;
    16: 
    17: 		v_sql := 'select count(*) from ' || v_view_name;
    18: 
    19: 		BEGIN
    20: 			EXECUTE IMMEDIATE v_sql;
    21: 			EXCEPTION WHEN OTHERS THEN
    22: 				dbms_output.put_line(v_view_name || ' fails select * test');
    23: 		END;
    24: 	END LOOP;
    25: END;