Forum Stats

  • 3,874,263 Users
  • 2,266,716 Discussions
  • 7,911,794 Comments

Discussions

Including SYSDATE in a spool file name

AnnieM
AnnieM Member Posts: 58 Red Ribbon

Greetings!

I want to set a spool file name to 2 variables, plus SYSDATE.

It works for the 2 variables:

define PIDM1 = '12059500';

define PIDM2 = '12177237';

spool c:\temp_amm\&PIDM1._&PIDM2.BANPROD_data.txt

(file name is: 12059500_12177237BANPROD_data.txt)

Through a google search, I found that the typical set up to include SYSDATE is as follows 

col date_stp new_value date_stp noprint

select to_char(sysdate,'yyyy_mm_dd') date_stp from dual;

spool c:\temp_amm\&date_stp.&PIDM1._&PIDM2.objects_data.txt

But it does not work for me; I receive a message box to provide the value for date_stp

Any thoughts on how to set this to work?

Later in the coding, I have the following set:

set echo off

set verify off

set feedback off

set headings off

set trimspool on

set termout on


Running in Oracle SQL Developer Version 19.2.1.247

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Thank you so much for your time and help!

Tagged:

Best Answer

Answers

  • Günter
    Günter Member Posts: 112 Bronze Badge

    Works for me, check with "show define" your settings

    set define "&"

    define PIDM1 = '12059500';

    define PIDM2 = '12177237';

    col date_stp new_value date_stp noprint

    select to_char(sysdate,'yyyy_mm_dd') date_stp from dual;

    spool c:\temp\&date_stp._&PIDM1._&PIDM2.objects_data.txt

    spool off

    AnnieM
  • KayK
    KayK Member Posts: 1,740 Bronze Crown
    edited Jun 10, 2021 6:28AM

    Hi Annie

    your script works for me. So i expect there must be some customizung in your SQL Developer.

    I prefer SQL*Plus so my SQL Devloper works out of the box, i even don't know the settings.


    regards

    Kay

    AnnieM
  • AnnieM
    AnnieM Member Posts: 58 Red Ribbon

    Thank you, both!

    I added set define "&"

    And it still err'd so I infer since it works for other people, it must be a server or settings issue. I have not done any customization of settings within Oracle SQL Developer.

    I can look at the settings and see if anything looks like an issue.

    Again, thank you so much!

  • KayK
    KayK Member Posts: 1,740 Bronze Crown
    Answer ✓

    Hi Annie,

    i can reproduce your issue:

    But only if i execute every single line with strg+enter or this icon:

    Try to execute the whole script with F5 or this icon:


    regards

    Kay

    AnnieM
  • AnnieM
    AnnieM Member Posts: 58 Red Ribbon

    Thank you, Kay!

    Yes! It now works if I select all and then press F5.

    Originally I was executing from outside the script, using this command:

    @"T:\USC_DBS\amatula\Banner\SQL_Scripts\Banner_ID_Tables.sql"

    But it is much easier to select all and press F5 within the script so I get the timestamp.

    I do not know you figured it out but I very much appreciate your time and help!

    Thank you, again, and all the best,

    Annie

    KayK