Toad World® Forums

Toad removes db prefix from stored procedures code in compare sync script

When Toad generates compare sync script, it apparently removes source db name from stored procedures code in the script.

So if we originally had an sp:

create or replace my_sp()

begin

select * from source_db.my_tables;

end;

Toad will remove the “source_db” part as such, although it’s a major part of the sp logic:

create or replace my_sp()

begin

select * from my_tables;

end;


Also, didn’t find an option to disable it that worked.

Hi Yoni,

this db name removal works as follows => If db name prefix within procedure code is same as the db that the procedure is defined within then we are removing the db name.

The purpose is that if we are deploying all changes (procedure and objects it refers to) to differently named DB then leaving db name prefixes would make it invalid.

On the other hand if referenced objects are moved then removing prefix for objects residing in same schema will make it work as expected.

Anyway we would like to know if there is a specific need for you to keep the prefixes and also if you would appreciate the option to keep them.

We could then make it part of future release.

Regards

Petr

Petr,

When I saw the original post question I thought to myself, “Uh oh,” but your explanation shows that it was, in my opinion, a well thought out feature which makes A LOT of sense rather than an oversight. Kudos!

Well, it could be useful for most cases I guess, but there’s another case:

so my source/reference database for comparison is called “db_x” and it has a SP called “query_from_db_x_sp

which has this SQL inside:

begin

select * from db_x.tbl;

end;

Table “tbl” only exists in “db_x” so on target comparison database “db_y”, I want to have the same SP code exactly so it will always query from “db_x.tbl”.

But Toad Edge sync script will not include the database prefix in the SP query.

Can you add an option to not auto remove the database prefix?

It will also prevent from false differences showing up on comparison.


Hi Yoni,

we have considered your request and will incorporate new option to turn prefix removal on/off in future release. Probably 2.0 (in first half of 2018) as 1.2.0 is going out shortly and we will not be able to include it.

BR

Petr