Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Fareed.

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

Answered by: Tom Kyte - Last updated: October 28, 2015 - 1:57 pm UTC

Category: Developer - 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 we 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


and you rated our response

  (6 ratings)

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

Reviews

this solution does not work in all cases ...

June 24, 2011 - 11:09 am UTC

Reviewer: Sokrates

... 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

Followup  

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.

:-)

June 24, 2011 - 1:17 pm UTC

Reviewer: Sokrates

Nice Example !

June 26, 2011 - 7:00 pm UTC

Reviewer: Fareed Akhlaq from Australia

Worked. Thanx

Worked perfectly!

April 30, 2015 - 2:44 pm UTC

Reviewer: Deatrice from Providence, RI

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

What about in SQL Developer?

October 27, 2015 - 8:48 pm UTC

Reviewer: Peter from Maryland

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

Followup  

October 28, 2015 - 1:57 pm UTC

Same as SQL*Plus, run:

set define off


first

SET DEFINE OFF will not work

October 28, 2015 - 4:03 pm UTC

Reviewer: Peter from Maryland, USA

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