Solved

Auto Generate Reports from Oracle DB

Posted on 2012-04-11
7
542 Views
Last Modified: 2012-04-23
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?
0
Comment
Question by:Talitha30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37834394
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37834403
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
 

Author Comment

by:Talitha30
ID: 37834464
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 74

Expert Comment

by:sdstuber
ID: 37834492
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
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 500 total points
ID: 37844150
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37844154
writing to with utl_file would create a report on the database server, not the client's machine.
0
 
LVL 5

Accepted Solution

by:
Sanjeev Labh earned 500 total points
ID: 37844881
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to Comment Out Lines of Code in a Pass Through Query In MS Access 2016 19 96
Oracle encryption 12 70
Database Design Dilemma 6 71
SQL Syntax Question 9 55
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question