Export Oracle Table to CSV through Scheduler

yes

To add to the above post:  Yes it is possible.

You didn't provide any specifications so we cannot even being to tell you what we would recommend.

For starters:
How large is the table?
What is the maximum row length?
Can any of the columns contain a comma?
What are the data types of the columns?


I would probably create a SQL script using string concatenation and schedule it through the OS job scheduler.

You can also write some PL/SQL using UTL_FILE and schedule it inside the database with DBMS_SCHEDULER.

There are many examples of both methods on the Internet.

my table contain column with datatype as number only and max number in tables rows may be 20000. Can you show me example for that.

create a .sql script and execute it from sqlplus.

set pages 0
set timing off
set feedback off
set lines 5000
spool myfile.csv
select col1 || ',' || col2 || ',' || col3 from table;
spool off

Select allOpen in new window

What OS are you using?

Windows 2007. I want to export in CSV format.

I understand you want CSV output.  Oracle's export utilities don't generate CSV.  You need some other tool or product to generate it.

Take the sql script I provided above and use sqlplus to execute it.

Is oracle's export utilities generate Excel Sheet?

no, just text files, xml, html, ...
just plain text

Oracle's provided export utilities generate a proprietary binary file to be used by the import utilities.  There is the classic exp/imp and new datapump  expdp/impdp.

To get what you want, again, you'll need some other tool.  Oracle doesn't provide one.

Oracle's SQL Developer may be able to create CSV files.  SQL Developer is a free GUI product from Oracle than you can download and install.  It is not part of the Oracle database software. SQL*Plus is part of the database software, and it certainly can create CSV files as slightwv suggested.  But, it is a command-line (non GUI) tool.

I didn't mention SQL Developer because I don't believe it can be scheduled to create a CSV.

>>SQL*Plus is part of the database software,

It too can be downloaded as part of the Instant Client.

You are correct, SQL Developer doesn't do scheduled jobs.  It can help to create and test *.SQL scripts that can then be deployed to run via either the database job scheduler, or an O/S job scheduler.

Is Oracle 9i Client Database Generates CSV automatically?
If Yes then Please let me know how it works.

We cannot keep saying it:  I know of no Oracle provided tool that can be scheduled to automatically create a CSV.

You will need to write some code.  Even if that code is a sqlpus script.

every monday i go to work and look on my desk for the button: "do everything automatically"
so far, i haven't found it yet

now I'm clear that generate CSV automatically without writing any Script or code isn't possible in Oracle.
So I have to Export Table manually by right click on table name and then select export and then i have to choose Format (i.e. CSV).

But now my question is how oracle is doing this thing? what They are using? Is it a  Tool or Code?

@slightwv (䄆 Netminder)
how do i execute a Script that u provided earlier.

you are doing the export in SQL Developer
that means you also have some sort of oracle client installed

create a file c:\temp\testcsv.sql with this content

set lines 500 pages 10000 feedback off
spool c:\temp\yourfile.csv
select owner||','||table_name from all_tables;
spool off;

Select allOpen in new window

from a command box:

sqlplus user/pass@db @c:\temp\testcsv.sql

Select allOpen in new window

change the script to your liking and then schedule it with task scheduler
if all works, you should have a csv file in your c:\temp directory

assuming c:\temp exists