• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

oracle sql Create multiple spool files one for each month


We have a query like this (ofcourse the real query is way more complicated than that)

SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET COLSEP "|"
SET TRIMSPOOL ON
SET LINES 300
spool cash_amount_jan_2009.txt

Select col1,col2
From table
Where month=’01’
And year=’2009’

Spool off


What is the best way to handle a request to produce spool files for all months since 2009 till now.
Like parametrizing the filter;or using pl/sql.

Please adise
0
it-rex
Asked:
it-rex
1 Solution
 
slightwv (䄆 Netminder) Commented:
Issue multiple spool commands and repeat the queries for each month in the script.


spool Jan.txt
select --- for Jan

spool Feb.txt
select --- for Feb

...
0
 
it-rexAuthor Commented:

:) I have alreday done that
they are asking for some kind of automation;
0
 
slightwv (䄆 Netminder) Commented:
Any way you do it will be ugly.

You can use PL/SQL and UTL_FILE or possible write some OS script.

You can also have SQL generate the SQL, spool it out, and execute the new script.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
schwertnerCommented:
You can also try the FOR statement of OS shels and to pass parameters to SQL*Plus script that are monts and years.
The problem of my suggestion is that shell scripting languages except sophisticated like PERL are not so flexible as procedural language.
So the PL/SQL approach proposed above is the most flexible and native for Oracle.
0
 
it-rexAuthor Commented:
any other ideas,anyone?
0
 
parparovCommented:
so, perl is not an option?
0
 
it-rexAuthor Commented:
it is an option,
0
 
it-rexAuthor Commented:
schwertner:,slightwv how plsql can do it?
0
 
slightwv (䄆 Netminder) Commented:
>>how plsql can do it?

Create a loop for the month numbers, use UTL_FILE.FOPEN to open a file using the generated number.

Then create a CURSOR loop for the select using the generated number for the month and use UTL_FILE.PUT_LINE to write out the values.

These functions are in the online docs.
0
 
it-rexAuthor Commented:
thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now