OraFAQ Forum: Client Tools » Exporting data with Scheduled job

Home » Other » Client Tools » Exporting data with Scheduled job (Oracle Database 10g Release 10.2.0.3.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Exporting data with Scheduled job [message #616680]

Thu, 19 June 2014 12:27

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

I am trying to learn how to use the Scheduler > Jobs Wizard in SQL Developer to schedule a SQL script to be exported in Excel to a file share. I can't find this option in the Wizard dialogue. Please suggest?

Report message to a moderator

Re: Exporting data with Scheduled job [message #616681 is a reply to message #616680]

Thu, 19 June 2014 12:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am trying to learn how to use the Scheduler > Jobs Wizard in SQL Developer to schedule a SQL script to be exported in Excel to a file share.
please post of the "SQL script"

what does "exported in Excel" mean?

Does the data originate in Oracle DB or in Excel?

Report message to a moderator

Re: Exporting data with Scheduled job [message #616682 is a reply to message #616681]

Thu, 19 June 2014 12:36

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

I would like to be able to create a SQL query and then schedule an automated repeating job that exports the data into Excel and saves the Excel file on a network share. I was studying this topic and noticed that Oracle quotes, "All Oracle starting at 10g come with out of the box scheduling". However, it seems that exporting data to a network share file is not part of the capability. Or is it? What tool(s) do I need to actually export reports to a network or email location?

Data is in Oracle

[Updated on: Thu, 19 June 2014 12:37]

Report message to a moderator

Re: Exporting data with Scheduled job [message #616684 is a reply to message #616682]

Thu, 19 June 2014 12:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Excel can connect directly to Oracle database via ODBC and issue SQL against the database.

Oracle does not have the capability to write Excel formatted file; which is a proprietary Microsoft format.

when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sched.htm#ARPLS72235

UTL_FILE http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/u_file.htm#ARPLS069 can read & write text files from/onto OS file system

Report message to a moderator

Re: Exporting data with Scheduled job [message #616685 is a reply to message #616684]

Thu, 19 June 2014 12:44

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

Thank You, seems like a different scheduling tool is most useful then.

Report message to a moderator

Re: Exporting data with Scheduled job [message #616688 is a reply to message #616682]

Thu, 19 June 2014 12:48

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Oracle's scheduler is just a scheduler. It submits code to be executed at a certain time or times.

What that the nature of that code and what it does is up to you. It could be an anonymous PL/sQL block. It could be a PL/SQL package. It could be an OS command -- say, referencing an OS batch/shell script. It's up to you.

One thing the scheduler, or any gui interface to it will NOT do is present you with a pre-packaged option to 'export data to an excel file'.

So you want to "export" data from the database to an "Excel" file. Do you truly mean an Excel file .. a file written in a binary format that is read only by Excel? Or do you mean a text file written to the XML format, which can also be read by Excel? or do you mean a text file written to the 'text delimited' (commonly comma-delimited) format, which can also be read by Excel, but carries no formatting information? What you really want will drive the technique needed to get it. Once that has been determined and the code written and tested, it is trivial to schedule the execution of that code in oracle's scheduler.

Report message to a moderator

Re: Exporting data with Scheduled job [message #616689 is a reply to message #616685]

Thu, 19 June 2014 12:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Thank You, seems like a different scheduling tool is most useful then.
different than what specifically?

DBMS_SCHEDULER was first released in V10 Oracle & V10 is now obsoleted & unsupported

Report message to a moderator

Re: Exporting data with Scheduled job [message #616690 is a reply to message #616688]

Thu, 19 June 2014 12:51

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

Thank you for the response Ed. I did mean something similar to what I have used in Crystal or SSRS, where the program creates an Excel file or PDF with the report output, then delivers it to an email or a network share.

I do see your point that almost totally regardless of the final solution for delivery (even if not in Oracle at all), writing and testing the SQL is the first step anyway (if I understood you right).

Report message to a moderator

Re: Exporting data with Scheduled job [message #616691 is a reply to message #616690]

Thu, 19 June 2014 12:55

ipisors
Messages: 24
Registered: May 2014
Location: USA [Arizona ... Desert]

Junior Member

Quote:

different than what specifically?

DBMS_SCHEDULER was first released in V10 Oracle & V10 is now obsoleted & unsupported

When I am in SQL developer, there is a part of the tree called Scheduler > Jobs > Job Wizard. I did not realize this was considered "obsolete and unsupported". https://drive.google.com/file/d/0BzU5xb14i1fsVkNUYU44cnhLOWs/edit?usp=sharing

Do you have any helpful suggestions?

Report message to a moderator

Re: Exporting data with Scheduled job [message #616692 is a reply to message #616690]

Thu, 19 June 2014 12:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What tool(s) do I need to actually export reports to a network or email location?
Oracle has a separate Report writing tool which is another (complex) product.
SQL*Plus can provide reporting capability in the hands of a competent operator.

>Do you have any helpful suggestions?
I don't think you now have anything to actually schedule.

SQL Developer is a separate & distinct product from Oracle RDBMS.

[Updated on: Thu, 19 June 2014 13:00]

Report message to a moderator

Previous Topic:	Check historical data/Monitor Temp tablespace usage
Next Topic:	Hebrew displayed as junk

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 16:02:31 CST 2025