When using the HTML Schema Doc generator manually, we can select objects based on a query: By using 'Specify object set' in 'In/Exclusions' and adding objects using the 'Load rows to Grid' functionality and then tick 'View/edit query before executing'. This is fine for manual usage. However, using the same setup in the automation designer, this loads the same set of objects every time the automation is run.
Is there a way to automate generating schema docs for all objects matching a given query? Note: just tables/view/procedures is not enough.
I am fine with a solution that requires writing an sqlplus script, but the 'specify object set' file format is not human-readable, so I can't generate that using sqlplus, or can I?
Welcome to the ToadWorld community for Toad/Oracle!
You're right, the "SOS" file (Specify Object Set) is not readable, and I don't think there's a way to generate it, other than through the HTML Schema Doc wizard.
Also, the Automation script definitions are saved in a file called ToadActions.dat ( C:\Users\YOUR_WINDOWS_USERNAME\AppData\Roaming\Quest Software\Toad for Oracle\YOUR_VERSION_TOAD\User Files\ToadActions.dat`) and it looks like this file is not readable... was just seeing if we could alter this file to either change the SOS query in some way. So no go here as well.
For now---assuming that you have a manageable, finite list of queries each of which brings back some desired set of included/excluded database objects---what I'd do is to create a bunch of those SOS files (one for each of your Object queries) and then specify the desired SOS file before you run the automation job (you can right-click on the Doc task in your automation flow and choose Properties.
Alternatively, you can create different automation jobs/Apps, each of which runs the HTML doc generator with a different SOS file specified for input.
Thanks for your answer. I don't think I expressed our use case clearly enough. The query is always the same. The difficulty is that our schema is subject to change. New objects do appear regularly, so every time that happens, we would have to generate the SOS file (great acronym by the way) anew manually. This does not happen when the automation is run. (In fact, I am not sure the query is saved at all in the automation, just the SOS.) It is then easier and robuster to simply do the whole thing manually via the wizard.
Anyway thanks for looking into the option of changing ToadActions file, I hadn't thought of that.
I don't know of a way to create that file with SQLPlus or anything else. And I don't know why I didn't make that SOS file a regular text file when I wrote that code. I should have. I'll make a change so regular text is the default file format.
Finally, you probably are aware of this, but just incase.....If you don't specify any objects at all on the "specify object set" page, then all objects in the schema will appear in the output.