Solved

Seeking Oracle Tools to Schedule SQL scripts

Posted on 2012-03-16
10
399 Views
Last Modified: 2012-03-16
I am currently running Oracle EE 10.2.0.5.0 on Sun OS 5.10 Sun box and need a tool to schedule SQL script jobs to run against the database at night, weekly, monthly and quarterly.    I want then the output to save automatically to csv or txt and possibly PDF.   I am spending too much time trying to run reports after hours when I know there must be something out there that will allow me to automate this process and allow me to get some sleep.  If possible, I  can then securely email and/or SFTP the output file as well.  Any ideas, thoughts or suggestions?  I would run this tool from my Windows workstation or server.  I am basically looking for a solution to do all of the above with minimal human intervention.

I need this right away.

Thanks
0
Comment
Question by:rmartes
[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
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 37728762
Have you tried using crontab to schedule a certain script to run at night time ?

But then you would need to code such script in order to output the report in a manner that suits you best.

I've done such job in the past on a mysql database (i'm pretty sure it's doable in oracle also) that was running a query format the output as XML format and then email it to me. The XML would be then processed with BI publisher and exported as PDF - minimal human interaction.

You would require basic bash scripting (script would run on your sunos machine) and awk. BI publisher installed.

I'll try to find the scripts i've coded and get back to you - not sure if i can still find then tho. Maybe they will give you an idea/concept on how to approach this.
0
 

Author Comment

by:rmartes
ID: 37728771
I am not fimilar with bash scripting, so that's why I was looking for an Oracle or third-party tool that would do this for me.  Even if that means several tools to accomplish everything.  I would  also perfer to not run on the database server if at all possible.  You know what I mean??
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 37728833
---need a tool to schedule SQL script jobs to run against the database at night, weekly, monthly and quarterly.    


There is no need for a tool, you can use the following three ways

1. Create a cronjob in unix
2. Create a database job
3. Use DBMS_Scheduler to do the task(Recommended)

using these things, you can schedule a job to run at particular intervals of time..

follow these links to get an idea on dbms_scheduler which is present in oracle 10g

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm

http://www.dba-oracle.com/t_dbms_scheduler_examples.htm


---I want then the output to save automatically to csv or txt and possibly PDF.

You can mention it in the program or procedure you do, to redirect the output to a csv file or txt file using utl_file package

or if you are using simple sql statements, then you can use spool commands in which will redirect the output of your sql to mentioned files..


after the files get generated, you can mail them too, using unix utilities

mail command to send an email and uuencode command to attach the attachment in mail

alternatively you can use utl_mail package in oracle to do this task..
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:rmartes
ID: 37728868
Thanks!!

Here is the problem.   I have never used and/or tried cronjobs, created database jobs or the DBMS_Scheduler.   This will require for me to learn, which I don't have the time with all my responsibilites.   Unfortunately, I am the only Oracle person and I am a jack of all trade with many hats and a small IT department.  I am the only one creating the various Oracle reports and emailing them to upper management.   The weekly reports take me about 45 minutes to run and the monthly/quarterly reports take approximate 2-3 hours in all.   This is why I was hoping for a tool that would do this all for me on a schedule.   I will in essence just put my script and bingo there is goes.. lol...   I will start looking at the links you provided, but I do need something quick with a short learning curve.  I do appreciate again your quick response.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 37728945
try this application monitor from manage engine
http://www.manageengine.com/products/applications_manager/

it takes 20 minutes to install and 1 minute per database
0
 
LVL 23

Expert Comment

by:David
ID: 37729000
If you're too busy to learn on the job, perhaps one of us can be hired to do this for you......  But a tool to meet your specifications -- to my knowledge it does not exist.
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 37729099
dvz does have a point there.
0
 

Author Comment

by:rmartes
ID: 37729458
Lol.. Thanks guy.   I guess I will start this evening and hit books over the weekend, since there are no tools that will do it for me.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 37729469
depends on what reports you want ...
0
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 37729874
Probably custom financial reports :)
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

728 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