SELECT
( case
when object_type = 'VIEW' THEN 'ALTER VIEW ' || object_name || ' COMPILE;'
when object_type = 'PROCEDURE' THEN 'ALTER PROCEDURE ' || object_name || ' COMPILE;'
when object_type = 'FUNCTION' THEN 'ALTER FUNCTION ' || object_name || ' COMPILE;'
when object_type = 'PACKAGE' THEN 'ALTER PACKAGE ' || object_name || ' COMPILE; ALTER PACKAGE ' || object_name || ' COMPILE BODY;'
when object_type = 'TRIGGER' THEN 'ALTER TRIGGER ' || object_name || ' COMPILE;'
else '' END) as script_item
FROM dba_objects
WHERE status = 'INVALID' and owner='AIMSUSER'
ORDER BY owner, object_type, object_name;
( case
when object_type = 'VIEW' THEN 'ALTER VIEW ' || object_name || ' COMPILE;'
when object_type = 'PROCEDURE' THEN 'ALTER PROCEDURE ' || object_name || ' COMPILE;'
when object_type = 'FUNCTION' THEN 'ALTER FUNCTION ' || object_name || ' COMPILE;'
when object_type = 'PACKAGE' THEN 'ALTER PACKAGE ' || object_name || ' COMPILE; ALTER PACKAGE ' || object_name || ' COMPILE BODY;'
when object_type = 'TRIGGER' THEN 'ALTER TRIGGER ' || object_name || ' COMPILE;'
else '' END) as script_item
FROM dba_objects
WHERE status = 'INVALID' and owner='AIMSUSER'
ORDER BY owner, object_type, object_name;