How do you compile your wrapped functions

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Wed, 22 Oct 2014 16:09:57 -0400
Message-ID: <CAGYrQyuTEYg_uWfCrQDzKnn_pVDc5ukrPqkNY_0oOsLzKkAM7w_at_mail.gmail.com>

I d on't understand why Oracle don fix the recompilation of wrapped procedures and the problem of the links (I cna't mention here).

it works until 11g, and I'm adequating to 12c, because it has the editionable and noneditionable additional words.

This scripts recompiles,
generates a ddl and adds an enter at the end for wrapped files then the rest of no compiled files
and finally enables the triggers, etc.

I was asking if you have a better method to recompile wrapped files.

Thank you:)

execute utl_recomp.recomp_serial();

REPLACE (
REPLACE (
REPLACE (

DBMS_METADATA.GET_DDL( CASE WHEN OBJECT_TYPE='PACKAGE BODY' THEN 'PACKAGE' ELSE OBJECT_TYPE END,OBJECT_NAME,OWNER)||CHR(10)||'/'||CHR(10)||CHR(10) , 'CREATE OR REPLACE PACKAGE BODY', CHR(10)||'/'||CHR(10)||CHR(10)||'CREATE OR REPLACE PACKAGE BODY')
, 'CREATE OR REPLACE EDITIONABLE PACKAGE BODY' , CHR(10)||'/'||CHR(10)||CHR(10)||'CREATE OR REPLACE PACKAGE BODY') , 'CREATE OR REPLACE NONEDITIONABLE PACKAGE BODY', CHR(10)||'/'||CHR(10)||CHR(10)||'CREATE OR REPLACE PACKAGE BODY')   CMD , /**/ 'XXXYYYYDDMMSSSSS' /**/ DAZ_VERSION FROM sys.procedure$,DBA_OBJECTS A
WHERE object_id = obj# AND STATUS = 'INVALID' --AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE','PACKAGE')
AND NOT bitand(options,2) = 0 /*WRAPPED*/
/

execute utl_recomp.recomp_serial();
-- unidades que no se han podido compilar para revisión SELECT B.REL_COD,
DECODE( OBJECT_TYPE,
          'PACKAGE BODY', 'ALTER PACKAGE ' || OWNER||'."'||OBJECT_NAME || '" COMPILE BODY;',

          'TYPE BODY',    'ALTER ' || 'TYPE' || ' ' ||
OWNER||'."'||OBJECT_NAME || '" COMPILE BODY;',
          'ALTER ' || OBJECT_TYPE || ' ' || OWNER||'."'||OBJECT_NAME || '"
COMPILE;' ) RECOMPILAR,B.ULTVER,
          /**/ 'SYS2010092752026' /**/ DAZ_VERSION

FROM
DBA_OBJECTS A, DAZ.ULTVER_UDP B
WHERE B.DSV_SISTEMA(+)=A.OWNER

AND B.DSV_NOMBREUNIDAD(+)=A.OBJECT_NAME
AND A.STATUS = 'INVALID'
AND A.OBJECT_TYPE NOT IN ( 'SYNONYM' )

ORDER BY
OBJECT_TYPE,
OBJECT_NAME
/

begin
for a in (SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' LOGGING;' A

              FROM DBA_TABLES
             WHERE NOT LOGGING='YES'
               AND NOT OWNER IN ('SYSTEM','SYS','MDSYS','WMSYS','DBSNMP')

AND TEMPORARY='N'             UNION ALL
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' LOGGING;' A

              FROM DBA_INDEXES
            WHERE NOT LOGGING='YES' AND NOT (INDEX_TYPE='LOB' OR
OWNER='SYSTEM')
            UNION ALL
SELECT 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' ENABLE;' A
              FROM DBA_TRIGGERS
             WHERE NOT STATUS ='ENABLED'
            UNION ALL

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' ENABLE NOVALIDATE CONSTRAINT '|| CONSTRAINT_NAME||';' A

              FROM DBA_CONSTRAINTS
             WHERE NOT STATUS='ENABLED' ) loop

 execute immediate (a.a);
end loop;
end;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 22 2014 - 22:09:57 CEST