| How can i create multiple spool files in SQL [message #42577] |
Fri, 11 April 2003 06:35  |
Mark Regler
Messages: 9
Registered: April 2003
|
Junior Member |
|
|
Hallo everybody,
i'm looking for a way to create a multiple spool files based on the content of a particular field in a table. E.g. a table contains 100 records. These records contain a field (Code) with a value between 0 and 9. What i would like to do is spool a file with all records that have Code 0, aswell as a file with all records that have Code 1 and so on. Is this possible?
Many thanks for your help.
Mark Regler
|
|
|
|
| Re: How can i create multiple spool files in SQL [message #42579 is a reply to message #42577] |
Fri, 11 April 2003 15:17  |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
SQL*Plus SPOOL command can only keep one file open. So, when you issue a SPOOL command, the file that's currently open (if such exists) is closed and the next file is open. If the file name is the same, the SPOOL command will override it.
You can use a trick to execute your job though.
SQL> create table text_for_spool (value_column varchar2 (10));
Table created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('1');
1 row created.
Elapsed: 00:00:00.30
SQL> insert into text_for_spool values ('2');
1 row created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('1');
1 row created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('3');
1 row created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('3');
1 row created.
Elapsed: 00:00:00.10
SQL> insert into text_for_spool values ('4');
1 row created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('5');
1 row created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('2');
1 row created.
Elapsed: 00:00:00.20
SQL> insert into text_for_spool values ('6');
1 row created.
Elapsed: 00:00:00.20
SQL>
Now create a SQL script (call it do_my_job.sql) as follows:
SET PAGES 0
SET TIMING OFF
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL c:tempcreate_many_sppols.sql
SELECT 'SPOOL c:tempfile_containing_' || value_column || '.txt' || CHR (10) ||
'SELECT value_column FROM text_for_spool WHERE value_column = ''' || value_column || ''';' || CHR (10) ||
'SPOOL OFF'
FROM text_for_spool
GROUP BY value_column;
SPOOL OFF
@ c:tempcreate_many_sppols.SQL
SET TERMOUT ON
When you run this script, it will create a file called create_many_sppols.sql with the following content:
SPOOL c:tempfile_containing_1.txt
SELECT value_column FROM text_for_spool WHERE value_column = '1';
SPOOL OFF
SPOOL c:tempfile_containing_2.txt
SELECT value_column FROM text_for_spool WHERE value_column = '2';
SPOOL OFF
SPOOL c:tempfile_containing_3.txt
SELECT value_column FROM text_for_spool WHERE value_column = '3';
SPOOL OFF
SPOOL c:tempfile_containing_4.txt
SELECT value_column FROM text_for_spool WHERE value_column = '4';
SPOOL OFF
SPOOL c:tempfile_containing_5.txt
SELECT value_column FROM text_for_spool WHERE value_column = '5';
SPOOL OFF
SPOOL c:tempfile_containing_6.txt
SELECT value_column FROM text_for_spool WHERE value_column = '6';
SPOOL OFF
... and automatically execute it. The output of this job will be 6 TXT files, each containing the data you were interested in.
Regards
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
