Search code examples
oraclesqlplus

Prevent SQL*Plus from removing spaces from middle of variable's string value

I have a SQL*Plus report that needs its runtime in format "MM/DD/YYYY hh24:mi" printed in the title section of each page. So I did the following:

-- set variable run_time 
column rTime new_value run_time noprint
select to_char(sysdate,'MM/DD/YYYY hh24:mi') rTime from dual;

-- print contents
ttitle right 'RUN TIME: ' &run_time

But what gets printed out is the date & time with no space in between, like so:

RUN DATE: 04/07/202115:32

At first I thought maybe it had something to do with the date to char conversion, but it's the same problem with any string and no dates invovled. For example:

column testing new_value testing123 noprint
select 'blah blah' testing from dual;

ttitle left &testing123

Result is "blahblah". So again the space in the middle got discarded. Does anyone know how to prevent this behavior? Is there a SQL*Plus setting that needs to be set, or something I need to format in the "column" or ttitle sections?

Solution

  • From the documentation:

    Enter text in single quotes if you want to place more than one word on a single line.

    As in their examples, put the variable in side single quotes; so do:

    ttitle left '&testing123'

    or

    ttitle right 'RUN TIME: &run_time'