Get data out of Oracle to flat file

[nirasha]

My scenario is this:

I want to get data out of an Oracle Database, periodically, in a format that can be read by other people, not just another Oracle database.

Any suggestions?

using triggers etc???

Thanks in advance,
Nirasha

[dave_young]

You can use a select statement to format the data as a comma seperated file(.csv) that can be viewed in excel. Here is an example of how to write the statement:

spool d:\flat_file.csv
Select
column1||','||
column2||','||
column3
from table;
spool off

Is this what you are looking for?

[sambavan]

Or you can use the utl_file to save the data on to a flat file and put the procedure under a dbms_job, that get executed at the specified intervals

On the previous case, you have to use the cron job execute a script that in turn would execute the sql code for you.

Choice is yours.

Sam

[nirasha]

Kind of, but I guess I would need alot of triggers to kick this off. I need only updates. db to db would be easier, but I was asked to consider it this way, so to make it more robust. Also if there alot of tables, things might get complex.

Thanks,
Nirasha

[sreddy]

http://www.dbasupport.com/forums/sho...?threadid=5636

[nirasha]

Thanks so much you guys. All of this was loads of help.