Generate Schema Script

Hi John,

I have started testing the updates in this version you have added for me and have some comments to pass back your way. At this time, I have been generating the following objects: Functions, Packages, Procedures, Sequences, Tables, Triggers, Types and Views.

Probably the only major issue I have so far with the generation of scripts is the completed scripts do not have grants AND synonyms. If an object only has grants or only has synonyms, these are generated correctly. But if an object has both grants and synonyms, it appears to only generate the grants NOT the synonyms.

The other less important observations are:

  • every run has produced an empty 'JavaSources' directory
  • when combining specs/body into one script, empty 'PackageBodies' and 'TypeBodies' directories are created
  • when choosing to put indexes/constraints into the object script, empty indexes and constraint directories are also created

The empty directory creation is only cosmetic and easily dealt with.

I have not completed testing, but I thought I would send you what I have so far.

Thanks for your amazing work John.



Hi Dave,

Thanks for the follow up.

I'll look at these bugs today - especially the grants/synonyms one.


Hi Dave,

Here's what I'm seeing regarding grants and synonyms:

  1. No problems with grants
  2. Public synonyms are always created
  3. Private synonyms: Generate Schema Script only creates private synonym scripts for synonyms owned by the schema being exported. This is by design. So if you have private synonyms owned by other schemas, which refer to objects owned by the schema being exported - well, you'd have to export a schema script for the synonym owner to get create scripts for those synonyms. I hope that makes sense and is not a problem.

The empty "JavaSource" folder is fixed in the beta that was released this morning, and I'll make a change to 17.0 to fix the other empty directories. The folders will briefly appear as the schema script is being created, but then the empty ones will be deleted at the end or the process.

Edit: I was just looking at the code for extracting synonyms. I forgot we already had this option to extract private synonyms owned by other schemas in Compare Schemas, so adding this to Generate Schema Script is trivial. I'll add it to 17.0.


1 Like

Hi John,

Adding to interesting observations, I have found an issue that exists also in version

It appears. that if I try to generate a SPEC and BODY script combined, the completed script is not always completed but rather truncated towards the end of the 'BODY'.

This appears on generated files that are either over 125k in size or greater than 3000 lines.

The method I use in version
1 - login to database as required schema owner
2 - select object type - packages (lines must excess 3000)
3 - select required packages on the left
4 - select create script
5 - ensure one file per object
6 - ensure script options include keep spec and body in same file
7 - generate scripts

So note this is also happening under the new 'Generate Schema Scripts' functionality.

I have not found any 'formatting' limitations under options other than:
options -> Scripts -> Script Output -> Limit results to -- lines, this is set to 0


HI Dave,

I am not aware of this bug but will investigate today and get back to you


I tried to reproduce this by adding about 25,000 lines worth of comments in both a package spec and body. I then turned on the "combine" option and the entire code base was there no problem.

I do have an idea though.

Is it possible that you have a CHR(0) character somewhere in your source? That character tells windows programs "this is the end of the text", so it causes text to get cut off.

The easiest way to look for it is using this SQL.

select *
from dba_source
where instr(text, CHR(0)) > 0

Hi John,

Thanks for your help. On further investigation, I've determined that the issue was on my end and not with Toad.

I was viewing these generated scripts through the Windows preview pane in explorer which does have a limit to the number of lines it can display. When viewed by text editors such as Notepad++, all lines are present and accounted for.

I can only apologize for the inconvenience I caused you and the time you spent investigating this issue I reported,

I wanted to ensure this was clarified to prevent any further unnecessary troubleshooting efforts on your part.

Again, my apologies.


Hi Dave,

It's fine. It only took me about 5 minutes. You have nothing to apologize for. The actual bugs you found more than made up for it. I appreciate you testing this stuff and letting me know what you find.


1 Like