RE: Changing object schema prefix references in plsql code

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Thu, 9 Jun 2011 18:34:08 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B17241593745F08D643_at_GVW1337EXC.americas.hpqcorp.net>

Extract the source into files and run the files through sed then reapply the updated source to database.

Editing a few scripts is neighter that hard nor time consuming when there are only a dozen. When it gets to be a hundred then code generation is my choice. You could extract the code from dba_source and use a carefully written replace to substitute the new owner as one option to consider.

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patrice sur GMail Sent: Thursday, June 09, 2011 2:23 PM
To: oracle-l_at_freelists.org
Subject: Changing object schema prefix references in plsql code

I did it manually, but am wondering what larger sites do in this situation.

I imported some schemas from a very old db (8i) into a newer (11gR2) one but had to change the name of one schema.

The objects ended up in the new schema name all right, but plsql code in other schemas still included the old schema name as prefix for some objects.

If we could edit source$ it would be a cinch to change all references from the old schema name to the new one.

If I could have connected from 11gR2 to 8i using imp, I could have remapped objects though am not sure whether remap actually does change schema name references.

What do larger sites do when they have to change schema names in plsql? Do they edit each reference manually?

Is there an undocumented switch that would let one edit source$...

Maybe I overlooked a better, obvious way to do this. If yes, don't hesitate to let me know.

^_^

Regards,

• Patrice

My profiles: [http://images.wisestamp.com/facebook.png] <http://www.facebook.com/home.php?#!/profile.php?id=100000206805521> [http://images.wisestamp.com/linkedin.png] <http://ca.linkedin.com/pub/patrice-boivin/a/933/5a9> [http://images.wisestamp.com/twitter.png] <http://www.twitter.com/PatriceBoivin> <http://www.twitter.com/PatriceBoivin>
Signature powered by WiseStamp<http://www.wisestamp.com/email-install>

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

Received on Thu Jun 09 2011 - 13:34:08 CDT