There was an import done from a prod 11g R2 schema into its corresponding dev schema, but they forgot to update all the db link references from the PROD server (SRV-PRD001) to the DEV server (SRV-DEV001). So I need to get all the DDL to compile from our packgaes and mviews that contain the db link reference to re-compile the objects. Tried the following:
SELECT
tmp.meta
FROM
(
SELECT DBMS_METADATA.GET_DDL(
object_type => 'PACKAGE_BODY',
schema => 'WH01',
NAME => DO.object_name
) meta
FROM dba_objects DO
WHERE object_type IN ('PACKAGE BODY')
AND owner = 'ALPHA'
UNION
SELECT DBMS_METADATA.GET_DDL(
object_type => 'MATERIALIZED_VIEW',
schema => 'WH01',
NAME => DO.object_name
) meta
FROM dba_objects DO
WHERE object_type IN ('MATERIALIZED VIEW')
AND owner = 'ALPHA'
) tmp
WHERE dbms_lob.instr(tmp.meta,'SRV-PRD001') > 0;
1) The individual sub queries work but not with a union and 2) the WHERE clause even with only one of the sub queries does not seem to work.
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
-----
ORA-31603: object "/2273a6c1_OidCalculator" of type PACKAGE not found in schema "WH01"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""
*Cause: The specified object was not found in the database.
*Action: Correct the object specification and try the call again.
/2273a6c1_OidCalculator is a java class so I'm not sure why that is even popping up...