Tuesday, January 26, 2016

How to compile invalid objects in oracle

SELECT CASE object_type
  2         WHEN 'PACKAGE' THEN
  3          'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
  4         ELSE
  5          'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
  6         END
  7    FROM dba_objects
  8   WHERE STATUS = 'INVALID'
  9    and owner = 'TESTCMS'
 10     AND object_type IN ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');

CASEOBJECT_TYPEWHEN'PACKAGE'THEN'ALTER'||OBJECT_TYPE||''||OWNER||'.'||OBJECT_NAM
--------------------------------------------------------------------------------
ALTER PACKAGE TESTCMS.BCE_DOWNLOAD_LIST_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_ITEM_DISPLAY_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_EDIT_CONTENT_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_IMAGE_GALLERY_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_RSS_FEED COMPILE BODY;

For function and procedure use:
alter function TESTCMS.object_name compile;
alter trigger schema.object_type compile;
alter procedure schema.object_type compile;

alter trigger PORTAL.WWDOC_DOCU_BRU_TRG compile;

=====================
TESTCMS                        BCE_CMS_NOTIFICATION_PORTLET                                                                                                     PACKAGE BODY
TESTCMS                        BCE_CMS_APPROVAL                                                                                                                 PACKAGE BODY

ALTER PACKAGE TESTCMS.BCE_CMS_NOTIFICATION_PORTLET COMPILE BODY;
ALTER PACKAGE TESTCMS.BCE_CMS_APPROVAL COMPILE BODY;

No comments: