Home » SQL & PL/SQL » SQL & PL/SQL » Simple way of doing spooling within spooling?
Simple way of doing spooling within spooling? [message #250389] Mon, 09 July 2007 21:42 Go to next message
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 #250392 is a reply to message #250389] Mon, 09 July 2007 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
You seem fixated/obsessed with writing PL/SQL (& EXCEUTE IMMEDIATE) when simple SQL can accomplish the same task.

spool setup_filename.sql
SELECT 'SPOOL I:\2007\ALM_2007\ALM_WORK_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'\ACS_EMEA_' || TO_CHAR(SYSDATE,'YYYYMMDD')|| '.txt'
FROM dual;
spool off
@setup_filename.sql
-- proceed with whatever else you want to do!
Re: Simple way of doing spooling within spooling? [message #250417 is a reply to message #250389] Tue, 10 July 2007 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need dbms_sql or PL/SQL just use:
spool &full_path;
SELECT 'ACS_EMEA_'|| Var_Value FROM OUTPUT_PROCESS_VARIABLES WHERE Var_Name = 'rundate';
spool off


Regards
Michel

[Edit] Sorry, Ana, didn't see your answer.

[Updated on: Tue, 10 July 2007 00:31]

Report message to a moderator

Re: Simple way of doing spooling within spooling? [message #250621 is a reply to message #250417] Tue, 10 July 2007 12:27 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Michel,

Sorry I don't think this is working.

&full_path will have to be defined already. I think the solution is really not going to be a simple solution.
Re: Simple way of doing spooling within spooling? [message #250625 is a reply to message #250621] Tue, 10 July 2007 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain more in details what you want to do.
Maybe with an example.
Maybe explaining why what I posted does not work for you...

Regards
Michel
Re: Simple way of doing spooling within spooling? [message #250649 is a reply to message #250625] Tue, 10 July 2007 13:48 Go to previous messageGo to next message
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 #250650 is a reply to message #250649] Tue, 10 July 2007 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand, put an example of execution.
I don't see in any place you set neither RUNDATE_MMDD nor rundate so I don't know or can't imagine what you can have.

Regards
Michel
Re: Simple way of doing spooling within spooling? [message #250653 is a reply to message #250389] Tue, 10 July 2007 14:07 Go to previous messageGo to next message
Bill B
Messages: 1482
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 Go to previous messageGo to next message
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 #250660 is a reply to message #250653] Tue, 10 July 2007 14:19 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Nope the whole purpose of introducing rundate explicitly is that we have no guarantee the sysdate is the actual date of execution. But thanks..

[Updated on: Tue, 10 July 2007 14:21]

Report message to a moderator

Re: Simple way of doing spooling within spooling? [message #250662 is a reply to message #250389] Tue, 10 July 2007 14:27 Go to previous messageGo to next message
Bill B
Messages: 1482
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

Re: Simple way of doing spooling within spooling? [message #250665 is a reply to message #250659] Tue, 10 July 2007 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You finally give us the question when you got the solution.
Curious way of asking.

Regards
Michel

[Updated on: Tue, 10 July 2007 14:39]

Report message to a moderator

Re: Simple way of doing spooling within spooling? [message #250675 is a reply to message #250662] Tue, 10 July 2007 15:30 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
Thanks, Bill.

I forgot the keyword ACCEPT. Razz
Previous Topic: ORA-22992 Error
Next Topic: update changes all records!
Goto Forum:
  


Current Time: Fri Dec 02 12:37:58 CST 2016

Total time taken to generate the page: 0.12321 seconds