CODEWITHSUNDEEP
oracle-databasesqlplus
Phil Goldenberg
Phil Goldenberg

Reputation: 78

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?

Upvotes: 0

Views: 138

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

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'

Upvotes: 1

Related Questions