How to grant tables,views,synonyms and packages to other schema in Oracle Apps R12.2.X ?

TABLES,VIEW,SYNONYM FOR OKL:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
      ,OWNER
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OWNER||'.'||GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


TABLES,VIEW,SYNONYM FOR OKC:
============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('TABLE','VIEW','SYNONYM')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('ALL',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

GRANTING OKL PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKL%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/


GRANTING OKC PACKAGES TO XXEC:
==============================

DECLARE
CURSOR C_GRANT is
SELECT OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE in ('PACKAGE')
   AND STATUS='VALID'
   AND OWNER NOT IN 'XXEC'
   AND OBJECT_NAME LIKE 'OKC%';
BEGIN
for GRANT_OBJ in C_GRANT
loop
AD_ZD.GRANT_PRIVS('EXECUTE',GRANT_OBJ.OBJECT_NAME,'XXEC');
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OOPS SOMETHONG WENT WRONG PLEASE FIND BELOW ERROR MSG');
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

How to complie all objects of a schema in Oracle ?

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures,
functions, packages, and triggers in the specified schema.
The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

How to compile an invalid views and triggers by using dynamic sql in oracle ?

--TO COMPILE THE INVALID VIEWS IN APPS&XXEC SCHEMA'S.

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'VIEW'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'VIEW' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

--TO COMPILE THE INVALID TRIIGERS IN APPS&XXFIN SCHEMAS

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type
                  FROM   all_objects
                  WHERE  object_type = 'TRIGGER'
                  AND    status != 'VALID'
                  AND    owner in ('APPS','XXEC')
                  ORDER BY 2)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'TRIGGER' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        NULL;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

How to compile an invalid Package Spec and Package Body by using dynamic sql in oracle ?

BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   all_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  AND    owner IN ('APPS')
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

how to alter invalid objects by using altercommand in Oracle ?

alter MATERIALIZED VIEW APPS.OE_ITEMS_MV compile;
alter procedure XXFIN.XXFIN_PROC COMPILE;
ALTER FUNCTION XXFIN_XXFIN_FUN COMPILE;
alter view APPS.XXFIN_VIEW COMPILE;
ALTER SYNONYM XXEC.OKL_STRM_TYPE_V COMPILE;
alter type APPS.CSI_ITEM_INSTANCE_OBJ compile;
alter package APPS.ARW_SEARCH_CUSTOMERS compile body;
alter package APPS.CSI_WEB_SERVICE_PKG compile body;
alter trigger APPS.XXFIN_TRX_TRIGGER COMPILE;

How to compile an invalid objects in Oracle?

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

How to find an invalid objects count in Oracle Apps ?

select  COUNT(*),owner, object_type
from    all_objects
where STATUS = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner, object_type;