-- packages.RefreshFromExport.sql
-- Purpose: Creates an import .par file from your INVALID database packages.
-- -----------------------------------------------------------------------------
SET VERIFY OFF
COL DROP_CMD FORMAT a75
COL owner FORMAT a15
COL status FORMAT a15
COL object_name FORMAT a30
clear screen
PROMPT +---------------------------------------------+
PROMPT | Create Commands to Refresh INVALID Packages |
PROMPT +---------------------------------------------+
PROMPT
ACCEPT v_Schema PROMPT 'Schema [SCOTT]:' DEFAULT 'SCOTT'
ACCEPT v_DirObj PROMPT 'DirObj [U03]: ' DEFAULT 'U03'
ACCEPT v_DP_File PROMPT 'DP File [xDB.MyDB.%U.dmp]: ' DEFAULT 'xDB.MyDB.%U.dmp'
-- Create DROP Commands
SELECT 'DROP PACKAGE ' || owner || '.' || object_name || ';' AS "DROP_CMD"
FROM dba_objects
WHERE status = 'INVALID' AND owner = '&v_Schema' ORDER BY owner, object_name;
-- Create Import .par File
PROMPT
PROMPT For packages you need to refresh:
PROMPT
PROMPT 1. Run DROP commands.
PROMPT
PROMPT 2. Create\run below import script.
PROMPT
PROMPT # impdp system PARFILE=impPackages.par
PROMPT DIRECTORY=&v_DirObj
PROMPT DUMPFILE=&v_DP_File
PROMPT LOGFILE=impPackages.log
PROMPT SCHEMAS=SCOTT
PROMPT INCLUDE=PACKAGE:" IN()"
PROMPT
PROMPT You can use\edit the below for the .par INCLUDE PACKAGE IN() clause.
PROMPT
SET HEADING OFF
SELECT '''' || object_name || ''',' AS "PACKAGES"
FROM dba_objects WHERE status = 'INVALID'
AND owner = '&v_Schema' ORDER BY owner, object_name;
SET HEADING ON
