1

I have the sql script at the beginning I put:

column dt new_value mydate noprint
select to_char(sysdate. 'YYYYMMDD') dt from dual;
spool &mydate.test.log

try to spool the log with the timestamp.

However, when I do this, the spool out file name like &mydate.test.log

Anyone has idea of what I've missed here?

Thanks in advance!

jarlh
  • 42,561
  • 8
  • 45
  • 63
TJY
  • 11
  • 1
  • 4

2 Answers2

2

In SQL*Plus, check the define setting with

show define

Normally, that should give something like 

define "&" (hex 26)

however, it is possible to set it to a different character, or disable it altogether, in which case you would see

define OFF

and to re-enable it you would need to

set define on

If you want to retain the . between the date and test.log, you will need two dots, as the first one terminates the substitution variable:

SQL> spool &mydate..test.log
SQL> spool
currently spooling to 20180830.test.log
William Robertson
  • 15,273
  • 4
  • 38
  • 44
1

You need 

SET define ON

For example:

SQL> column dt new_value mydate noprint
SQL> select to_char(sysdate, 'YYYYMMDD') dt from dual;

SQL> set define off
SQL> select '&mydate' from dual;

'&MYDATE'
--------------------------------
&mydate

SQL> set define on
SQL> select '&mydate' from dual;

'20180830'
--------------------------------
20180830

Here you find something more.

Aleksej
  • 22,443
  • 5
  • 33
  • 38