Toad World® Forums

Set Reports Manager on multiple databases ?

Hello,

I’m a begginer at Toad and my work is to make reports with a couple of queries on my databases, like sum up all autoextends of the tablespaces…

But the thing is, I only found the possibility to make it on one database per time, is there a way to make it global to all databases connected ? Or do I have to do them all one by one, everytime I have a report to do ?

Thanks you,

Best regards.

Mat

Hi Matt,

There are many different ways to do what you want. I would recommend using the Automation Designer (Utilities Menu) and use the Execute as Script action
for the different database connections you need to run the queries on. You can then export the results to whatever format you want. You can also schedule to run the apps/actions you create whenever you want. This is probably the most efficient method -
you can find more about Automation Designer in the Help.

Probably the simplest would just to use the ‘connect’ command and run as a script in the Editor (using F5) - connecting to each db you want to run the queries
on.

Eg.

connect
username/pwd@db1

select
*

from
scott.emp;

connect
username/pwd@db2

select
*

from scott.emp;

etc.

There are various other options in Toad as well, like saving your queries to SQL Recall and just running them as you need. The best thing I could suggest
would be for you to go thru the help and become familiar with the different features Toad provides to find what best works for you.

Hope this helps.

Dennis

From: tendrills [mailto:bounce-tendrills@toadworld.com]

Sent: Tuesday, July 30, 2013 3:56 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Set Reports Manager on multiple databases ?

Set Reports Manager on multiple databases ?

Thread created by tendrills

Hello,

I’m a begginer at Toad and my work is to make reports with a couple of queries on my databases, like sum up all autoextends of the tablespaces…

But the thing is, I only found the possibility to make it on one database per time, is there a way to make it global to all databases connected ? Or do I have to do them all one by one, everytime
I have a report to do ?

Thanks you,

Best regards.

Mat

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Just one more note about this - if you do decide to use Automation Designer and are just running queries - the Export Dataset action might work best for
you if you want to export the result separately to different formats, like Excel, Delimited text, HTML, etc.

Note that Mark wrote an excellent doc on the automation designer and there were several blogs about it on Toad World. So check them out – and all the great
stuff on Toad World J

From: Dennis Paulus [mailto:bounce-Dennis_Paulus@toadworld.com]

Sent: Tuesday, July 30, 2013 6:56 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Set Reports Manager on multiple databases ?

RE: Set Reports Manager on multiple databases
?

Reply by Dennis Paulus

Just one more note about this - if you do decide to use Automation Designer and are just running queries - the Export Dataset action might work best for you
if you want to export the result separately to different formats, like Excel, Delimited text, HTML, etc.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Dennis and Bert, and thank you for your answers.

When I use the script mode, I got my output in txt file, but I’d prefer it in a much better output like excel or even html, so I tried the Export Dataset action, but when I use “connect username/pwd@db1”, it doesn’t like it at all.

I only can use it for the active connection.

Isn’t there an option to get reports by sql using the same method as in Backup Reporter ? Like, you click on the connection that you want your report to be ?

Mat

Hi Mat,

Yes, in Automation Designer, if you use the ‘Export Dataset’ action, you simply put your query on the Dataset tab (no need to include the connect string),
and then on the options tab select the format you want to export it to (Excel, HTML, etc.). For the connection part - in the upper left hand corner there is a connection button that will control what database it is run on. Add multiple actions for the different
db’s you want to run your queries on.

So, if you want to run it on multiple databases do the following:

  1. Add ‘Export Dataset’ app

  2. Set the connection to the database you want. Set the output options. Paste your query.

  3. Add another ‘Export Dataset’ app

  4. Set the connection to another database you want. Set the output options. Paste your query.

– Repeat for all databases and queries you want

Then to run, you can…

  1. Run the whole app and it will produce all your output files in one run

  2. Run the individual actions as you want

  3. Or schedule the App/actions to run automatically whenever is best for you, even if you are not at your computer.

The little script I sent before is only if you don’t care about format and to give you an example of how the same query can be run on multiple db’s using
a script. I recommend using the AD for the best results and ease of use. It’s a very powerful tool.

Hope this helps,

Dennis

From: tendrills [mailto:bounce-tendrills@toadworld.com]

Sent: Wednesday, July 31, 2013 5:26 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Set Reports Manager on multiple databases ?

RE: Set Reports Manager on multiple databases
?

Reply by tendrills

Hi Dennis and Bert, thank you very much, it worked !

But if I may ask, is there any way to get the results in a “beautiful” pdf, using a Report Def File or something ?

Because my only output is a txt file.

Or maybe a way to call a report from reports manager in an app in automation designer ?

Mat

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Mat - see my last email - there is no need to use the connect string when using the Export Dataset action, as the connection button (upper Left hand corner)
takes care of that for you.

From: tendrills [mailto:bounce-tendrills@toadworld.com]

Sent: Wednesday, July 31, 2013 5:40 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Set Reports Manager on multiple databases ?

RE: Set Reports Manager on multiple databases
?

Reply by tendrills

Hi Dennis and Bert, and thank you for your answers.

When I use the script mode, I got my output in txt file, but I’d prefer it in a much better output like excel or even html, so I tried the Export Dataset action, but when I use “connect username/pwd@db1”,
it doesn’t like it at all.

I only can use it for the active connection.

Isn’t there an option to get reports by sql using the same method as in Backup Reporter ? Like, you click on the connection that you want your report to be ?

Mat

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

When you are in Toad Editor and have a SQL command whose output you like in the data grid – but now want that output as a PDF file – just do right-hand-mouse
menu on data grid and choose report. Now you’re thrown into Toad complete report designer utility with your query sent over. Should take just a few mouse clicks to choose your report header, layout, footers, etc. as you would do in any report designer tool
– then choose run and finally save as PDF file. Takes like 30 seconds from start to finish ….

From: tendrills [mailto:bounce-tendrills@toadworld.com]

Sent: Wednesday, July 31, 2013 4:26 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Set Reports Manager on multiple databases ?

RE: Set Reports Manager on multiple databases
?

Reply by tendrills

Hi Dennis and Bert, thank you very much, it worked !

But if I may ask, is there any way to get the results in a “beautiful” pdf, using a Report Def File or something ?

Because my only output is a txt file.

Or maybe a way to call a report from reports manager in an app in automation designer ?

Mat

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hi Dennis,

Thanks again !

Just one little thing, do you know if is there a way to have them all in the same file ?

Thank you,

Mat

edit : Thank you too Bert, didn’t knew about this way !

Let’s say that you using the AD Export Dataset Actions to export your results and you want to export to an Excel Instance. There is a sheet name field which
you could populate with separate names (eg your dbname), and then run the app and it’ll export the results from each action to a separate sheet.

Every format has different options associated with it, so you really just have to play with it to find what works best for you. If you run it as a script,
then you can easily do this, but then your formatting is limited. AD is a very powerful tool, so it really depends on what you want to do with it and how much time you want to spend and how creative you want to be
J.

From: tendrills [mailto:bounce-tendrills@toadworld.com]

Sent: Wednesday, July 31, 2013 10:11 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Set Reports Manager on multiple databases ?

RE: Set Reports Manager on multiple databases
?

Reply by tendrills

Hi Dennis,

Thanks again !

Just one little thing, do you know if is there a way to have them all in the same file ?

Thank you,

Mat

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Hello,

I already tried this setup, but unfortunatly, it still replace the file by a new one everytime.

Thank you anyway :slight_smile:

Created an entry in the idea pond for this for people to vote on

toadfororacle.ideascale.com/…/440565-8477

Sorry - I didn’t see the response to this. Did you try using ‘Excel Instance’ as the output format, and then setting the ‘Sheet name’ field?