# Generate Schema Script: Sort for Creation Problem

**URL:** <https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [February 17, 2010, 10:42am UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262 "2010-02-17T10:42:54Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![dclamage\_468](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dclamage_468/32/87_2.png) [@dclamage\_468](https://forums.toadworld.com/u/dclamage_468)\
**Post date:** [February 17, 2010, 10:42am UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/1 "2010-02-17T10:42:54Z")

</div>

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

> **[Investment Management Data Automation | Confluence](https://www.confluence.com/en-us/)**
>
> Confluence delivers automated solutions for critical fund administration and reporting processes such as regulatory reporting, financial statement preparation and supports a wide array of global investment types.

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.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [February 17, 2010, 11:14am UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/2 "2010-02-17T11:14:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dclamage\_468](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dclamage_468/32/87_2.png) [@dclamage\_468](https://forums.toadworld.com/u/dclamage_468)\
**Post date:** [February 17, 2010, 1:50pm UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/3 "2010-02-17T13:50:51Z")

</div>

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

> **[Investment Management Data Automation | Confluence](https://www.confluence.com/en-us/)**
>
> Confluence delivers automated solutions for critical fund administration and reporting processes such as regulatory reporting, financial statement preparation and supports a wide array of global investment types.

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.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [February 17, 2010, 2:36pm UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/4 "2010-02-17T14:36:44Z")

</div>

Thanks Dan, this should be fixed next beta.

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [February 17, 2010, 3:39pm UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/5 "2010-02-17T15:39:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [February 17, 2010, 3:49pm UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/6 "2010-02-17T15:49:42Z")

</div>

Just noticed this:

---

<div class="post-metadata">

**Author:** ![dclamage\_468](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dclamage_468/32/87_2.png) [@dclamage\_468](https://forums.toadworld.com/u/dclamage_468)\
**Post date:** [February 18, 2010, 7:24am UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/7 "2010-02-18T07:24:32Z")

</div>

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

> **[Investment Management Data Automation | Confluence](https://www.confluence.com/en-us/)**
>
> Confluence delivers automated solutions for critical fund administration and reporting processes such as regulatory reporting, financial statement preparation and supports a wide array of global investment types.

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.

---

<div class="post-metadata">

**Author:** ![HillbillyToad](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/hillbillytoad/32/190_2.png) [@HillbillyToad](https://forums.toadworld.com/u/HillbillyToad)\
**Post date:** [February 18, 2010, 8:57am UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/8 "2010-02-18T08:57:04Z")

</div>

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](http://www.toadworld.com/KNOWLEDGE/ToadKnowledge/CoffeeBreakBytes/tabid/73/TID/22)  
6/cid/71/Default.aspx

---

<div class="post-metadata">

**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\
**Post date:** [February 18, 2010, 10:39am UTC](https://forums.toadworld.com/t/generate-schema-script-sort-for-creation-problem/9262/9 "2010-02-18T10:39:38Z")

</div>

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