| Using dynamic filenames in SPOOL - help needed [message #9686] |
Mon, 01 December 2003 21:09  |
Prabha R
Messages: 21
Registered: October 2001
|
Junior Member |
|
|
Hi,
I need to spool data into different filenames based on the number of rows returned.i.e for every 10000 rows i need to spool the data into a new filename.
I was suggested to use the following code to acheive the same. But it is not working.
Set column v_filename;
select 'extract1.txt' as v_filename from dual;
spool &&v_filename
select sysdate from dual;
spool off;
The file is not getting created and no errors are also thrown.
Any immediate help will be greatly appreciated.
|
|
|
|
| Re: Using dynamic filenames in SPOOL - help needed [message #9690 is a reply to message #9686] |
Tue, 02 December 2003 00:26  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
To simply correct the code above, so that it creates a file named "extract1.txt" and spools the result of "select sysdate from dual;" to it:
column v_filename new_value v_filename
select 'extract1.txt' as v_filename from dual;
spool &v_filename
select sysdate from dual;
spool off
However, this does not spool 1000 rows each into separate files. In order to do that, first you must establish what constitues the first 1000 rows, second 1000 rows, and so forth, by ordering by something. Otherwise you may get the same or overlapping rows. If you run the script below, it will spool the first 1000 table_name's in the user_tables table, when ordered by table_name, into a file named extract1.txt. Then it will put the next 1000 in a file named extract2.txt and so on. You can simply copy and paste the entire code below into a .sql file, substitute your table name for user_tables and your columns for column_name, then start the script.
-- start of script to spool 1000 rows per file
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF
SPOOL extracts.sql
SELECT DISTINCT 'SPOOL extract' || CEIL (ROWNUM / 1000) || '.txt' || CHR (10)
|| 'SELECT table_name FROM ' || CHR (10)
|| '(SELECT table_name, ROWNUM rn FROM ' || CHR (10)
|| '(SELECT table_name FROM user_tables ORDER BY table_name))' || CHR (10)
|| 'WHERE rn BETWEEN '
|| ((1000 * CEIL (ROWNUM / 1000)) - 999)
|| ' AND ' || (1000 * CEIL (ROWNUM / 1000)) || CHR (10)
|| '/'
FROM emp
/
SELECT 'SPOOL OFF' FROM DUAL
/
SPOOL OFF
START saved_settings
START extracts.sql
-- end of script to spool 1000 rows per file
The script above works by using a form of dynamic sql also known as sql to create sql. Starting the script above creates and starts another script called extracts.sql that uses top-n analysis to get the rows you want and looks something like this:
SPOOL extract1.txt
SELECT table_name FROM
(SELECT table_name, ROWNUM rn FROM
(SELECT table_name FROM user_tables ORDER BY table_name))
WHERE rn BETWEEN 1 AND 1000
/
SPOOL extract2.txt
SELECT table_name FROM
(SELECT table_name, ROWNUM rn FROM
(SELECT table_name FROM user_tables ORDER BY table_name))
WHERE rn BETWEEN 1001 AND 2000
/
SPOOL extract3.txt
SELECT table_name FROM
(SELECT table_name, ROWNUM rn FROM
(SELECT table_name FROM user_tables ORDER BY table_name))
WHERE rn BETWEEN 2001 AND 3000
/
SPOOL OFF
I have also included code to save and restore the settings of your previous set commands, which must be temporarily altered, in order to create the extracts.sql file without headers and things that don't belong in it.
|
|
|
|
| Re: Using dynamic filenames in SPOOL - help needed [message #9826 is a reply to message #9690] |
Sat, 06 December 2003 07:45 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I just noticed that I mixed up my examples a little. I originally started to use the emp demo table, but then figured that not everybody has the demo tables, but everybody has user_tables. In my example, I missed replacing one emp with user_tables. Here is the corrected version:
-- start of script to spool 1000 rows per file
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF
SPOOL extracts.sql
SELECT DISTINCT 'SPOOL extract' || CEIL (ROWNUM / 1000) || '.txt' || CHR (10)
|| 'SELECT table_name FROM ' || CHR (10)
|| '(SELECT table_name, ROWNUM rn FROM ' || CHR (10)
|| '(SELECT table_name FROM user_tables ORDER BY table_name))' || CHR (10)
|| 'WHERE rn BETWEEN '
|| ((1000 * CEIL (ROWNUM / 1000)) - 999)
|| ' AND ' || (1000 * CEIL (ROWNUM / 1000)) || CHR (10)
|| '/'
FROM user_tables
/
SELECT 'SPOOL OFF' FROM DUAL
/
SPOOL OFF
START saved_settings
START extracts.sql
-- end of script to spool 1000 rows per file
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
