Why You Should Avoid Hard Coding Schema Names In Source Code.

Toad for Oracle
1

Afternoon All, this isn’t a bug report or complaint, just some advice based on a trashed test database!

I was requested to run an import of a few tables, into a test system, for some pre-production testing that needed large amounts of data. The data were imported using impdp, and there were various REMAP_SCHEMA and REMAP_TABLESPACE settings within the impdp parameter file to ensure that the import placed the objects into a separate user from the main test user, as some other testing (not performance related) was going on that didn’t want to be disturbed.

So the import was kicked off and as expected a few errors and warnings were raised due to roles and users etc, in production, that don’t exist in test, plus a few triggers refused to compile without errors - due to missing sequences.

Suddenly, the test team started raising questions about why my user’s triggers were invalid and causing their tests to fail with messages about the state of the trigger being invalid and it will not validate. (On the fly recompilation of the trigger failed, hence the messages.)

Looking into the situation, I noticed that the trigger code is like this:

create or replace trigger MYUSER.trigger_name
after insert or update
on TESTUSER.Table_name
…

While impdp has correctly remapped the trigger owner from the production username, to my username, it has not, and cannot do likewise for any source code (triggers, procedures, functions, packages, types, type bodies etc) which contains a hard coded schema name, so my user’s trigger has been added to the test user’s table, and because my trigger is broken, it broke the tests!

It would be an understatement to say that I was somewhat unpopular at this point!

So, as of now, anyone who adds the schema name to an object they own, is asking for trouble if that object can be imported into another schema name because it will invariable affect the original user’s objects, and may break things in strange and unusual ways!

You are advised to carefully consider your settings in Toad at View->Toad Options, then Editor->Open/Save on the left side, Object Loading on the right side. I suspect that “never include” is best to avoid this situation, and warn the developers not to add the schema name to objects that belong to that schema.

1 Like
2

Hi Norm,
Option to exclude that “feature” in scripts is nice way to get your point.

Also it ensures taht you should be in right schema when execution the script or, or as DBA (sysdba) are doing such a tasks from one DB to another I find schema name more than needed.
Brg
Damir

3

Morning Damir,

my problem was caused by the triggers on a table hard coding the schema name in the ON schema.table_name clause. BEcause, as the DBA, I was importing a dump file with remap_schema (and remap_tablespace) but because the trigger code hard coded the schema name, the invalid trigger in my schema, broke the testing because their inserts etc failed to execute my trigger.

That’s the problem with hard coding the schema name. [:’(]