Home » SQL & PL/SQL » Client Tools » spooling a file with filename and current timestamp
spooling a file with filename and current timestamp [message #243998] Mon, 11 June 2007 04:24 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i am running an sql file, my aim is to generate a log file
which shows the filename followed by current timestamp
i am using spool, but not getting it, please help

SET head off echo off termout off feedback off verify off linesize 228 trimspool on

SET head off echo off termout off feedback off verify off linesize 90 trimspool on wrap off

begin
dbms_output.put_line('hi1');

end;

spool test.log
set serveroutput off
SELECT 'define filename=test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' FROM dual;
spool off


/




what i want is a log file which has test_(current timestamp
appears here)

Re: spooling a file with filename and current timestamp [message #243999 is a reply to message #243998] Mon, 11 June 2007 04:26 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i mean, the logfile should have test_(current timestamp)
tht file should be created
Re: spooling a file with filename and current timestamp [message #244001 is a reply to message #243998] Mon, 11 June 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
col filename new_value filename
SELECT 'test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM dual
/
spool &filename
<do work>
spool off

Regards
Michel


Re: spooling a file with filename and current timestamp [message #244002 is a reply to message #244001] Mon, 11 June 2007 04:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You could have searched and found this thread.

MHE
Re: spooling a file with filename and current timestamp [message #244011 is a reply to message #243998] Mon, 11 June 2007 05:18 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member

col filename new_value filename
SELECT 'test_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM dual
spool &filename
begin
dbms_output.put_line('hi');
end;

spool off

/



i am confused now, i want to include this in some sql script
which has a declare, begin and end....

i am confused about where to place the example

cause using the above script, i am not getting the log file

goes to show my understanding regd set commands is poor

somebody please help
Re: spooling a file with filename and current timestamp [message #244028 is a reply to message #244011] Mon, 11 June 2007 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to include this in some sql script which has a declare, begin and end....

declare/begin/end is not SQL but PL/SQL.
column/spool is neither SQL nor PL/SQL it is SQL*Plus.
You can't use SQL*Plus commands inside PL/SQL.
You put SQL*Plus commands outside and then execute your SQL or PL/SQL work.

Regards
Michel

Re: spooling a file with filename and current timestamp [message #244040 is a reply to message #243998] Mon, 11 June 2007 07:12 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member

SET head off echo off termout off feedback off verify off linesize 228 trimspool on
SET serveroutput ON SIZE 999999

col filename new_value filename
SELECT 'gautamlog_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
fROM dual;

spool &filename;
begin
dbms_output.put_line('hi');
end;
/
spool off;





thanks !
Previous Topic: SQL and iSQL*plus
Next Topic: How to create a counter using Oracle SQL Developer?
Goto Forum:
  


Current Time: Tue Dec 06 13:57:35 CST 2016

Total time taken to generate the page: 0.09491 seconds