sqlplus returns leading carriage return into a variable

Shell Programming and Scripting
1

I am trying to generate some scripts to help manage an Oracle database. When I check the value returned from Oracle it has a leading carriage return in the variable. Is there a way to prevent this? Is there a way to easily strip out the carriage return. See code and output below.

LASTFILE=$(sqlplus -s "/ as sysdba" <<EOF
       set head off
        select file_name from dba_data_files
        where tablespace_name = upper('$TABLESPACE') and
        file_id in (select max(file_id) from dba_data_files 
        where tablespace_name = upper('$TABLESPACE'));
        exit
EOF)

echo "Last data file = $LASTFILE"

Output looks like this:

Last data file = 
/u01/oradata/database/file_name

The file name gets wrapped after the =

Any help will be greatly appreciated.

2

try

...
set head off
set linesize 150
...
3

I found this and it works.

LASTFILE=`echo $LASTFILE`

echo "Last file = $LASTFILE"

Output looks like this:

Last file = /u01/oradata/database/file_name

If anyone has a better way please let me know but this works.

4

give a try separating EOF and put the ending parenthesis on a new line
you could also give a try using back quote instead of the $( ) notation

5

It had the same issue with the back quotes (ticks). That is why I was trying the ().

Putting the back quote on the line after the EOF does not work either.

Using the echo command works and I guess I'll stay with that for now.

Thanks!!!!!

6

set newpage 0
set pagesize 0

?

---------- Post updated at 12:34 AM ---------- Previous update was at 12:18 AM ----------

you may also try to add the hyphen
<<-EOF
instead of
<<EOF

7

That works!! Much cleaner and less code.

Thank you!!!

8

with "set newpage 0", you still have the ^L character at the beginning. I always do set pagesize 0, you can also do "set newpage none".

1 Like