Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Fareed.

Asked: June 24, 2011 - 12:12 am UTC

Last updated: October 28, 2015 - 1:57 pm UTC

Version: 10.2.0

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I am running sql script and want to produce a spool file with a name as script_output||datetime i.e appended with the data time. I searched a lot but couldn't find a clear and proven answer.

Thanx
Fareed

and Tom said...

ops$tkyte%ORA11GR2> @test
ops$tkyte%ORA11GR2> set echo on
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column filename new_val filename
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select 'my_script_name_' || to_char(sysdate, 'yyyymmdd' ) filename from dual;

FILENAME
-----------------------
my_script_name_20110624

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> spool &filename
ops$tkyte%ORA11GR2> select 'hello world' from dual;

'HELLOWORLD
-----------
hello world

ops$tkyte%ORA11GR2> spool off
ops$tkyte%ORA11GR2> !cat &filename..lst
ops$tkyte%ORA11GR2> select 'hello world' from dual;

'HELLOWORLD
-----------
hello world

ops$tkyte%ORA11GR2> spool off

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> !ls -l &filename..lst
-rw-rw-r-- 1 tkyte tkyte 120 Jun 24 11:23 my_script_name_20110624.lst


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

this solution does not work in all cases ...

Sokrates, June 24, 2011 - 11:09 am UTC

... but only under the tacit assumption, that sqlplus option
define is set to its default &
(like many scripts provided by Oracle !)

Everyone is free to either
set define off

or
set define +

(for example)

So, in my eyes, it is a bug if you rely on a default setting implicitly.
Every script using
define &
should explicitly take care of this setting for example via
set define off
set define &


Tom Kyte
June 24, 2011 - 1:05 pm UTC

of course, if you change the defaults - if you change the way sqlplus works by default, if you disable or change a feature - you might have to accommodate for that.

But I also rely on ";" working - I rely on the defaults being in place. Everywhere

As does everyone pretty much.

ops$tkyte%ORA11GR2> set sqlterminator *
ops$tkyte%ORA11GR2> select * from dual*

D
-
X

ops$tkyte%ORA11GR2> 


better not show any queries without explicitly setting every sqlplus setting? It gets a little whacky after a while.

:-)

Sokrates, June 24, 2011 - 1:17 pm UTC

Nice Example !

Fareed Akhlaq, June 26, 2011 - 7:00 pm UTC

Worked. Thanx

Worked perfectly!

Deatrice, April 30, 2015 - 2:44 pm UTC

I have been looking for two days to make this work. Thank you so much!

What about in SQL Developer?

Peter, October 27, 2015 - 8:48 pm UTC

When I try to do this in SQL Developer, it grabs control and prompts me for a value. I can't turn off DEFINE. I though there was another way to stop SQL Developer from interpreting the DEFINE char. Any ideas?
Connor McDonald
October 28, 2015 - 1:57 pm UTC

Same as SQL*Plus, run:

set define off


first

SET DEFINE OFF will not work

Peter, October 28, 2015 - 4:03 pm UTC

If you turn it off, it won't substitute the value from the SELECT statement.

This works in both SQL*Plus and SQL Developer (need &&, not &)



>column filename new_val filename

>select 'my_script_name_' || to_char(sysdate, 'yyyymmdd' ) filename from dual;

>spool &&filename