Generate Schema Script: Sort for Creation Problem

There is a bug in the Sort for Creation involving public synonyms.
Unfortunately, we have a lot of “legacy” (i.e., written before I was
hired) code in triggers, stand-alone functions and procedures that reference
other subprograms via their public synonyms. This dependency is not being
considered when sorting the objects for creation. When I run the creation script
in an empty schema, I get the warning that many of the routines compile but with
errors, due to objects they reference through the public synonyms appearing
later in the create script. Then I’m forced to sift through the creation
script and manually rearrange the routines in proper dependency order.

This is true on both Toad 10.0 and the most recent 10.5 Beta.

The workaround is to run DBMS_UTILITY.compile_schema after the creation script.
But from our customers’ (and their DBA’s) point of view, a create
script shouldn’t have errors – you don’t get the warm, fuzzy
feeling that a clean create would provide.

Dan Clamage

Oracle Database Developer

C O N F L U E N C E

412.246.1806 direct

412.802.8632 main

412.802.8647 fax

THE MATERIAL CONTAINED IN THIS MESSAGE IS CONFIDENTIAL AND IS SUBJECT TO
RESTRICTIONS ON ITS DISCLOSURE. The recipient acknowledges that the information
contained herein is the exclusive, proprietary and confidential property of
Confluence Technologies, Inc. and shall be at all times regarded, treated and
protected as such by the recipient. The use and disclosure of this information
is subject to the restrictions contained in the Software License Agreement
between the recipient (or the recipient’s employer or its affiliates) and
Confluence Technologies, Inc.

Thanks Dan.

If you have a smallish schema script that I can reproduce this with, it will be
helpful if you can send it to me. If not, it sounds easy enough to reproduce.
I’ll take a look at it soon.

-John

I just whipped up this simple example.

I run this as a script:

create or replace function func_f1 return integer as

begin

return ( 1 );

end func_f1 ;

/

CREATE or REPLACE PUBLIC synonym f1 for func_f1 ;

CREATE or REPLACE FUNCTION f2 return integer as

begin

return ( f1 );

end f2 ;

/

Then run Generate Schema Script with Sort for Creation turned on.

This is the generated script. Function f2 will compile with errors because of
the reference to the public synonym f1, which doesn’t get created until
the end.

Prompt Function FUNC_F1 ;

– FUNC_F1 (Function)

CREATE OR REPLACE function func_f1 return integer as

begin

return ( 1 );

end func_f1 ;

/

Prompt Function F2 ;

– F2 (Function)

CREATE OR REPLACE FUNCTION f2 return integer as

begin

return ( f1 );

end f2 ;

/

Prompt Synonym F1 ;

– F1 (Synonym)

CREATE PUBLIC SYNONYM F1 FOR FUNC_F1 ;

I also tried unchecking Sort for Creation and got the same output.

As an aside, 10.5 Beta is missing the ability to save and retrieve all my
Generate Schema Script settings to/from a text file, as 10.0 has. I like that
feature, so please put it back in.

Dan Clamage

Oracle Database Developer

C O N F L U E N C E

412.246.1806 direct

412.802.8632 main

412.802.8647 fax

THE MATERIAL CONTAINED IN THIS MESSAGE IS CONFIDENTIAL AND IS SUBJECT TO
RESTRICTIONS ON ITS DISCLOSURE. The recipient acknowledges that the information
contained herein is the exclusive, proprietary and confidential property of
Confluence Technologies, Inc. and shall be at all times regarded, treated and
protected as such by the recipient. The use and disclosure of this information
is subject to the restrictions contained in the Software License Agreement
between the recipient (or the recipient’s employer or its affiliates) and
Confluence Technologies, Inc.

Thanks Dan, this should be fixed next beta.

Thanks. I just tested it in my local build…it should be fixed next beta.

Just noticed this:

I wish to share my settings with another developer (so he doesn’t have to
laboriously go through each screen and click/unclick items). How can he load the
settings I’ve created?

I wish to name (or otherwise capture) a set of settings so I can use them for
different purposes on-demand. How can I do that?

Dan Clamage

Oracle Database Developer

C O N F L U E N C E

412.246.1806 direct

412.802.8632 main

412.802.8647 fax

THE MATERIAL CONTAINED IN THIS MESSAGE IS CONFIDENTIAL AND IS SUBJECT TO
RESTRICTIONS ON ITS DISCLOSURE. The recipient acknowledges that the information
contained herein is the exclusive, proprietary and confidential property of
Confluence Technologies, Inc. and shall be at all times regarded, treated and
protected as such by the recipient. The use and disclosure of this information
is subject to the restrictions contained in the Software License Agreement
between the recipient (or the recipient’s employer or its affiliates) and
Confluence Technologies, Inc.

What version of Toad are you using?

We have a new excellent automation feature that allows you to easily share toad
‘jobs’ and create different types of toad activities that you can
run on demand or schedule.

http://www.toadworld.com/KNOWLEDGE/ToadKnowledge/CoffeeBreakBytes/tabid/73/TID/22
6/cid/71/Default.aspx

Hey Dan,

Go to options -> general to see where your ‘application data
directory’ is. In the folder under that, is a “User
Files” folder. You can zip that folder up and give it to your coworker
– they’ll have to look in their copy of Toad to see where it goes.
Copy/paste it with Toad not running. You might want to have your
coworker keep their connections.ini and connectionpasswords.ini files, otherwise
your saved connections will transfer, but not the passwords.

That should do it, but I’d have your coworker keep a copy of their old
user files folder just incase.

You can also use that option to change your user files folder, so you can
have a different set of options on demand.

-John