Auto Generate Reports from Oracle DB

Is there a way I can auto generate reports to run a certain day of the week using a script? If so, how can I accomplish this and what reporting tool would be good to use to do this?
Talitha30Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
As long as the reports are not overly complex, I suggest sqlplus.  It has some pretty powerful reporting capabilities.

Once you create the sqlplus script, you can schedule it with any scheduling tool your system has.

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_six.htm#i1081008
0
sdstuberCommented:
business objects will let you do it.

probably most reporting software could be invoked from a scheduler in some fashion.

or, if you're looking for a solution that is entirely within the database you could use a pl/sql procedure to generate your content and email the results as an attachment or inline, the procedure could then be scheduled via dbms_scheduler (10g and above) or dbms_job (9i and lower)

This article gives an example of how to send attachments including a sample that generates a simple csv report but the formatting could be as complex as you want.

http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html
0
Talitha30Author Commented:
I have created the sql query for my reports in SQL Developer, is there a way to auto generate my report from this tool?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
no, sqldeveloper has no command line scripting options.


you can invoke scripts from within it,  but you can't script the invocation of sql developer itself.

however,  if you've written something that sql developer can execute, then you should be able to use sql*plus as slightwv suggested
0
Sanjeev LabhDatabase ConsultantCommented:
For auto generating reports from an existing sql, the best approach would be by creating a procedure using the sql and write the output onto a file using utl_file in plsql.
For making this procedure run automatically after a definite interval submit a job for this procedure as per interval. Once each run accomplishes you can collect your report from the server where it has been written using UTL_FILE.
Any help regarding usage of UTL_FILE you can find it very easily on the net.
0
sdstuberCommented:
writing to with utl_file would create a report on the database server, not the client's machine.
0
Sanjeev LabhDatabase ConsultantCommented:
surely on the server. But if has to be run automatically it has to be run through a job, can't be run through client. Hence writing through utl_file on the server. Once written it can easily be downloaded. I do not see any mention by Talitha that it is to be accomplished by client only. Query regarding if SQL developer can auto generate the report then I think it is not possible to auto generate the report running through SQL developer client. Though it can generate report manual while running the query and saving the output in different formats. But this cannot be automated.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.