Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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