view recompile

SET SERVEROUT ON FOR WRA SIZE 50000
DECLARE
i1 PLS_INTEGER := 0;
i2 PLS_INTEGER := 0;
CURSOR c IS
SELECT view_name, text, text_length
FROM user_views, user_objects
WHERE object_type = 'VIEW'
AND object_name = view_name
AND status != 'VALID';
BEGIN
/* First loop: just try to recompile */
FOR r IN c
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER VIEW ' || r.view_name || ' COMPILE';
i1 := i1 + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( sqlerrm );
DBMS_OUTPUT.PUT_LINE( r.view_name );
i2 := i2 + 1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'Compile: ' || TO_CHAR( i1 ) || ' OK; ' ||
TO_CHAR( i2 ) || ' not OK.' );
i1 := 0;
i2 := 0;
/* Second loop: recreate */
FOR r IN c
LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || r.view_name || ' AS ' || r.text;
i1 := i1 + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( sqlerrm );
DBMS_OUTPUT.PUT_LINE( r.view_name );
i2 := i2 + 1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE( 'Recreate: ' || TO_CHAR( i1 ) || ' OK; ' ||
TO_CHAR( i2 ) || ' not OK.' );
END;
/