Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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