Re-import packages in Oracle

oracleoracle-11g-r2

I'm aware of options to re-import data to tables using table_exists_action and can give options like skip,append or replace for the data.

But is there an option to re-import packages (re-create) into the database if they are already existing without throwing up an error like

" Package PKG_1 already exists"

I basically have packages which have some changes in them and re-import to another environment where there packages are already existing.

Best Answer

According to https://community.oracle.com/thread/959980 and https://community.oracle.com/thread/838380 the only way to do this is to use the SQLFILE parameter to impdp to create a script. Then replace CREATE PACKAGE with CREATE OR REPLACE PACKAGE and then execute the script.

Related Solutions

Security for Application Developers doing PL/SQL work in Oracle

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server. Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.

In the case of our critical systems (SCT Banner, for tracking classes & students, and Oracle Financials), there were also 'test' and 'seed' servers. Test was for user acceptance testing before stuff migrated from dev to prod; 'seed' was stock install of the software, so should we find a bug, we could verify if it was something we had introduced or came from SCT or Oracle's software.

How to merge Oracle data

Just add a WHERE clause to your select that eliminates the duplicates:

INSERT /*+ APPEND */ INTO db1.myTable (
   SELECT * FROM db2.myTable b WHERE NOT EXISTS 
      (SELECT 1 FROM db1.myTable a WHERE a.ID = b.ID)
   );

If you don't have a field like ID that is unique across the two tables, then you will need to compare more fields, perhaps all of them.

Best Answer

Related Solutions

Security for Application Developers doing PL/SQL work in Oracle

How to merge Oracle data

Related Question