Home » SQL & PL/SQL » SQL & PL/SQL » oracle package referring old schema after migration to new schema (oracle 11g)
oracle package referring old schema after migration to new schema [message #616121] Thu, 12 June 2014 08:03 Go to next message
Shravan48
Messages: 4
Registered: April 2010
Junior Member
I used impdmp to import dump from OLD_SCHEMA to NEW_SCHEMA. All packages look fine except 10 packages, the error is that they refer to old schema i.e., the table names in them are being affixed with OLD_SCHEMA. i.e., OLD_SCHEMA.table name. I checked synonyms they look same as in correct packages. Is there a way to fix it?
Re: oracle package referring old schema after migration to new schema [message #616122 is a reply to message #616121] Thu, 12 June 2014 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Shravan48 wrote on Thu, 12 June 2014 06:03
I used impdmp to import dump from OLD_SCHEMA to NEW_SCHEMA. All packages look fine except 10 packages, the error is that they refer to old schema i.e., the table names in them are being affixed with OLD_SCHEMA. i.e., OLD_SCHEMA.table name. I checked synonyms they look same as in correct packages. Is there a way to fix it?


manually change the source code.
Re: oracle package referring old schema after migration to new schema [message #616123 is a reply to message #616122] Thu, 12 June 2014 08:06 Go to previous messageGo to next message
Shravan48
Messages: 4
Registered: April 2010
Junior Member
Thanks for quick response. It is a product Smile that is why i can't change code
Re: oracle package referring old schema after migration to new schema [message #616124 is a reply to message #616123] Thu, 12 June 2014 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You might be able to avoid error by create new synonym between OLD_SCHEMA.PACKAGE1 & NEW_SCHEMA.PACKAGE1; then again it might be that no solution is possible.
Re: oracle package referring old schema after migration to new schema [message #616126 is a reply to message #616123] Thu, 12 June 2014 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the code explicitly address a schema then you have no other solution than to change the code or to change the schema.

Re: oracle package referring old schema after migration to new schema [message #616144 is a reply to message #616123] Thu, 12 June 2014 09:24 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Developers need to learn how to write portable code. Never explicitly quality object name with schema name in definer rights stored code if object is owned by definer (stored code owner).

SY.

[Updated on: Thu, 12 June 2014 09:24]

Report message to a moderator

Previous Topic: Create the Sequence
Next Topic: Select Query
Goto Forum:
  


Current Time: Thu Mar 28 06:47:18 CDT 2024