Solved

oracle sql Create multiple spool files one  for each month

Posted on 2011-09-14
10
559 Views
Last Modified: 2012-05-12

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
Comment
Question by:it-rex
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538204
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
 
LVL 11

Author Comment

by:it-rex
ID: 36538221

:) I have alreday done that
they are asking for some kind of automation;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538253
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
 
LVL 47

Expert Comment

by:schwertner
ID: 36543309
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
 
LVL 11

Author Comment

by:it-rex
ID: 36543314
any other ideas,anyone?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:parparov
ID: 36543704
so, perl is not an option?
0
 
LVL 11

Author Comment

by:it-rex
ID: 36545370
it is an option,
0
 
LVL 11

Author Comment

by:it-rex
ID: 36545382
schwertner:,slightwv how plsql can do it?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36545413
>>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
 
LVL 11

Author Closing Comment

by:it-rex
ID: 36574642
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dataware house query tuning 9 65
ORA-12560: TNS:protocol adapter error 8 87
Oracle Next Available Number 2 32
Bash Script to Analyze Oracle Schemas 11 84
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now