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.
Here's what I'm seeing regarding grants and synonyms:
No problems with grants
Public synonyms are always created
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.
Adding to interesting observations, I have found an issue that exists also in version 16.3.231.2085.
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 16.3.231.2085:
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
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
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.
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.
I am using the latest Toad 17.1 and running the Generate Schema Script and all is working well except for one thing I can't seem to get around.
Many of the scripts are being generated with a heading, as an example...
--
-- CHECK_METER_EXCEPTIONS (Function)
--
and if I have synonyms included within the main script, they too have headings, ie...
--
-- GET_IPARM_ITEM (Synonym)
--
With Packages where the package and body are in the one script, I get a heading for both.
What I really want is to remove the headings (but keep script comments). I have looked in all places to try to get the scripts generated without these comments but I can't find anywhere to do this.
note that toad options -> Formatter -> Header is disabled. So too are all formatting options in options directly under the 'schema generate script' procedure.
