SQL & PL/SQL

You can create a job in OEM (Oracle Grid) or you can create a job using DBMS_JOB package or, if your version allows, using DBMS_SCHEDULER package. And yes, you can create a cron job.

SY.

Thank you . I do not have complete privileges in the oracle database . So do I have to ask the admin to run the dbms_job to execute the sql script at regular intervals ? And once I execute this sql script I want to save the results in an excel file ? Can I include this in the job ? If so how can do this as part of the automated process ?
How to know what kind of privileges I have been given as a user in an oracle database . Are there any extra privileges required to run automated jobs for an Oracle user ?
Thanks!

You would only have to run the DBMS_JOB.SUBMIT call (or the equivalent DBMS_SCHEDULER calls) once and then Oracle would execute the job at whatever schedule you specified. Of course, you would need to have EXECUTE privileges on the DBMS_JOB (or DBMS_SCHEDULER) package.

Is this a SQL*Plus script? PL/SQL? Something else? If you want to have the job run inside Oracle, it would need to be PL/SQL. You wouldn't be able to use SQL*Plus commands (i.e. spool). If the goal of the script is to generate a file, that would imply using the UTL_FILE package, creating an Oracle directory object on the database server, and writing the data to the file on the database server. If you have a SQL*Plus script, that may be a decent amount of effort in the conversion process.

Justin

Well, DBMS_JOB/DBMS_SCHEDULER will allow you to run PL/SQL code on database server, so output file will reside on database server box. OEM will allow you the same plus to run OS scripts on any box where OEM agent is installed.

SY.

Isn't OEM just using the DBMS_SCHEDULER package to run an external batch script? I wasn't aware that Enterprise Manager had the ability to run scripts outside of the DBMS_SCHEDULER package.

Justin

OEM allows to create two types of jobs. Actual OEM jobs and database jobs. Database jobs can be created by navigating to database target, Administration, Jobs. When you create database job, job is added to DBMS_SCHEDULER jobs on that particular database. BTW, if you create DBMS_SCHEDULER job directly on the database and then check database jobs from OEM you will see it there. Basically it is just an gui over database DBMS_SCHEDULER. OEM jobs, or repository jobs are created by clicking Jobs tab on main screen. These jobs can be of supported administrative tasks, e.g., backup, cloning, and patching jobs or user own custom OS and SQL scripts. OEM uses DBMS_JOBS for that. Repository view MGMT$JOBS provides OEM job info. OEM uses agent(s) to execute job on proper target(s).

SY.

Hi Justin
I am only trying automate a sql statement (select) not a Pl/sql block . Currently we are running that select statement manually in sql navigator where the results are temporarily stored in memory and then I export that result into an excel file. I want to automate that process.Can automate this process and store in an excel file from where its picked up by a sql server DTS package.I think I can automate this process but I am not sure whether I can save the results into an excel file outside the database?I know I store it as a table inside the database . But as mentioned I have minimum privileges what else shoyuld I looking for in this process ?
Thanks!

But you want to do more than automate the query-- you want to automate the process of running the query, fetching the results, and writing the results to a file that Excel can open. That is going to require something more than plain SQL.

- Excel is a binary, proprietary file format, so generating a true Excel file from PL/SQL is relatively hard. Depending on the version(s) of Excel you are trying to support, you may be able to use Excel's XML representation instead.
- You can potentially use the OWA_SYLK package to generate a SYLK format file, which Excel should be able to open.
- You could also use the UTL_FILE package to generate a CSV file with the data in it
- You could also write a SQL*Plus script that generates a CSV file by spooling the results of a query to a file.

The first three options would generate a file that Excel could read on the database server. The latter would generate a file that Excel could read on the client machine.

Justin

I don't know if this would suffice you, but you can do is to have a csv generated from the sql query (Append all the columns with a , in between) and then use an UTL File package to write to a text file with .csv as the extension (But this would make the output file within the database server. So further more - You can use utl_ftp to transfer this to any other server location (But security needs to be considered)

Once transferred CSV can be opened directly in Excel

And if you are using Oracle Applications (Sorry for assuming this) we can use XML Publisher directly to view the output in Excel by making the XML out of your query having it as a concurrent program


Thanks
Shasik

Edited by: Shasik on Nov 4, 2008 10:04 AM

Can I use the above mentioned DBMS_JOB/DBMS_SCHEDULER packages to automate my sql query ,store the results in a table on the database server and then use that table as the input to the DTS package for the sql server ? And if possible (guessing) using OEM is it possible to store the results of the sql query directly into an excel file outside the database server ?
Thank you!