2

I have a connection to an Oracle database via SQL Developer and I want to write a query that returns a monthly set of data and then extract that data to a delimited text file. I know how to do that just fine, what I am wondering is if there is a way to write a script to run the query and extract the data month by month for a year. That way I would kick off the script and whenever it all finishes I would have 12 text files, one for each month.

I could do it manually but it is a lot of data and I would like to have it run overnight. The reason for doing it this way is the application we would be using the data with would run faster if we did not try to import all of that data at once. I don't even know if it is possible but if so can someone point me in the right direction?

Thanks in advance.

user1723699
  • 1,031
  • 6
  • 13
  • 27
  • Show us how you extract the data to a delimited text file, and give us a script of your table and its data. – neshkeev Aug 07 '14 at 14:33
  • I would extract the data by going to the grid and right clicking and selecting extract to csv. The SQL query would just be something simple like: select * from All_Data where Date_Field between date'2014-01-01' and date'2014-01-31' – user1723699 Aug 07 '14 at 14:37
  • Once again, Could you please give us the table to deal with? – neshkeev Aug 07 '14 at 14:38
  • I was editing my post when you replied, I couldn't get the formatting to work properly but you should get the gist of it. Nothing special. – user1723699 Aug 07 '14 at 14:41

2 Answers2

2

First write your parameterised script:

define the_year=&1
define the_mon=&2

set lines etc
select * from the_table
where trunc(the_date , 'MM' ) = to_date ( '&the_year&the_mon', 'YYYYMM' )

spool extract_&the_year&the_mon.csv

/

spool off

Then a wrapper script:

@the_script 2014 01
@the_script 2014 02
.
.
.
@the_script 2014 12

You can get clever(ish) and generate the wrapper:

sppol the_wrapper.sql
select '@the_script ' || to_char ( ADD_MONTHS ( trunc(sysdate,'YYYY' ), rn-1 ), 'YYYY MM' )
from ( select rownum rn from dual connect by level < 13 );
spool off

DOn't forget the set options to make the generated script runnable (e.g. set verify off, set feedback off, etc).

TenG
  • 3,843
  • 2
  • 25
  • 42
0

Learn SQL*Plus, this is a really powerful tool for managing Oracle Database, if you start searching how to extract data from table to *.cvs file you will find, for example, this question right away

If you give me a script to create a table and fill it I will show you an example how to extract data from your table.

Community
  • 1
  • 1
neshkeev
  • 6,280
  • 3
  • 26
  • 47