| Simple way of doing spooling within spooling? [message #250389] |
Mon, 09 July 2007 21:42  |
dpong
Messages: 73
Registered: January 2007
|
Member |
|
|
Hi,
Obviously I'm new to the concept of spool a sql script by loading in the script...
Here is what I have tried. While I have seen people making use of spool file_name
@ sql script
spool off
, I'm not sure exactly if I need to do it the same way. Could I use DBMS_SQL to execute on the SELECT statement ? Any alternatives that is simpler ?
set heading off
set termout off echo off verify off trimspool on pagesize 0 linesize 2000
set feedback off
set serveroutput on size 1000000
col path new_value full_path
SELECT 'I:\2007\ALM_2007\ALM_WORK_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'\ACS_EMEA_' || TO_CHAR(SYSDATE,'YYYYMMDD')|| '.txt' path
FROM dual;
spool &full_path;
DECLARE
rundate VARCHAR2(8) ;
BEGIN
SELECT Var_Value INTO rundate
FROM OUTPUT_PROCESS_VARIABLES
WHERE Var_Name = 'rundate';
DBMS_OUTPUT.PUT_LINE('ACS_EMEA_'|| rundate);
--EXECUTE IMMEDIATE 'SELECT * FROM ACS_EMEA_'||rundate ;
END;
/
spool off;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Simple way of doing spooling within spooling? [message #250649 is a reply to message #250625] |
Tue, 10 July 2007 13:48  |
dpong
Messages: 73
Registered: January 2007
|
Member |
|
|
set heading off
set linesize 2000 pagesize 0
set feedback off echo off
set verify off termout off trimspool on
SET serveroutput ON SIZE 1000000
col path new_value fullpath
--col rundate FORMAT 99999999 new_value rundate
--col RUNDATE_MMDD FORMAT 0999 new_value RUNDATE_MMDD
/* rundate : 20070322
RUNDATE_MMDD: 0322
*/
SELECT 'I:\2007\ALM_2007\ALM_WORK_'|| &rundate || '\INT_FINAL_FILE_'|| &RUNDATE_MMDD ||'.txt' path
FROM DUAL ;
spool &fullpath;
select * from INT_FINAL_FILE_03_22; /* change table_name as needed*/
spool off;
The problem right now is the path will always chop off the leading 0 for RUNDATE_MMDD...
I'm not sure how to preserve it
|
|
|
|
|
|
| Re: Simple way of doing spooling within spooling? [message #250653 is a reply to message #250389] |
Tue, 10 July 2007 14:07 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Just a guess, but if rundate is when it is actually run then do the following.
set heading off
set linesize 2000 pagesize 0
set feedback off echo off
set verify off termout off trimspool on
SET serveroutput ON SIZE 1000000
col path new_value fullpath noprint
SELECT 'I:\2007\ALM_2007\ALM_WORK_'|| to_char(sysdate,'yyyymmdd') || '\INT_FINAL_FILE_'|| to_char(sysdate,'MMDD') ||'.txt' fullpath
FROM DUAL ;
spool &fullpath
select * from INT_FINAL_FILE_03_22; /* change table_name as needed*/
spool off; [Updated on: Tue, 10 July 2007 14:08] Report message to a moderator |
|
|
|
| Re: Simple way of doing spooling within spooling? [message #250659 is a reply to message #250653] |
Tue, 10 July 2007 14:18 |
dpong
Messages: 73
Registered: January 2007
|
Member |
|
|
Okay, I solved the problem
The problem was I ended up getting a filename with a less-than-desirable output.
INT_FINAL_FILE_322.txt
INT_FINAL_FILE_0322.txt is what I wanted
So here is my solution.
set heading off
set linesize 2000 pagesize 0
set feedback off echo off
set verify off termout off trimspool on
--SET serveroutput ON SIZE 1000000
col path new_value fullpath
--col rundate FORMAT 99999999 new_value rundate
--col RUNDATE_MMDD FORMAT 0999 new_value RUNDATE_MMDD
/* rundate : 20070322
RUNDATE_MMDD: 0322
*/
--SELECT '20070322' rundate FROM DUAL;
--SELECT '0322' RUNDATE_MMDD FROM DUAL;
SELECT 'I:\2007\ALM_2007\ALM_WORK_'|| &rundate || '\INT_FINAL_FILE_'|| lpad(&RUNDATE_MMDD, 4, '0') ||'.txt' path
FROM DUAL ;
spool &fullpath;
select * from INT_FINAL_FILE_03_22; /* change table_name as needed*/
spool off;
... after trying out FORMAT in SQL*Plus and all other means I could have thought of using...
|
|
|
|
|
|
| Re: Simple way of doing spooling within spooling? [message #250662 is a reply to message #250389] |
Tue, 10 July 2007 14:27 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
If it is going to be entered everytime, then try the following
set heading off
set linesize 2000 pagesize 0
set feedback off echo off
set verify off termout off trimspool on
ACCEPT RUNDATE DATE FORMAT YYYYMMDD PROMPT 'Enter rundate (YYYYMMDD):'
col path new_value fullpath noprint
SELECT 'I:\2007\ALM_2007\ALM_WORK_'|| &rundate || '\INT_FINAL_FILE_'|| substr(&rundate,5,4) ||'.txt' fullpath
FROM DUAL ;
spool &fullpath;
select * from INT_FINAL_FILE_03_22; /* change table_name as needed*/
spool off; [Updated on: Tue, 10 July 2007 14:30] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
