I have a variable whose value is found using sql query.
I want to remove the new line character from that variable since I want to concatenate this variable with the other. Below is the code:
dt=`sqlplus -s user/pwd@servicename <<EOF
set feedback off;
set head off;
select replace(to_char((sysdate-7),'YYYYMonDD')||'_'||to_char((sysdate-1),'YYYYMonDD'),chr(10), '') from dual;
exit;
EOF`
echo "test $dt"
If you are using bash, you can use Parameter Expansion:
dt=${dt//$'\n'/} # Remove all newlines.
dt=${dt%$'\n'} # Remove a trailing newline.
The following should work in /bin/sh as well:
dt="${dt%
}" # Remove a trailing newline.
${var//a/} you don't need to use last /, ${var//a} will do exactly the same.
dt=${dt//[$'\n\r']/} if you need to remove Carriage Returns as well
${dt%%$'\n'} still removes a single newline as there's no wildcard. You can use shopt -s extglob ; s=${s%%*($'\n')} though.
Sounds like you need "tr", something like:
echo ${dt} | tr -d '\n'
man tr for detail, as usual
This work on Linux (bash):
dt="$(echo "$dt"|tr -d '\n')"
On Linux, or other systems with GNU's date utility, this also works to get that value for dt: (without involving Oracle...)
dt="$(date -d 'yesterday' +%Y%b%d)_$(date -d '7 days ago' +%Y%b%d)"
echo 'puts [clock format [clock scan "-1 week"] -format %Y%b%d]_[clock format [clock scan yesterday] -format %Y%b%d]' | tclsh
from the Oracle sqlplus manual
SET PAGES[IZE] {14 | n} Sets the number of lines on each page of output. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.
so add a set pagesize 0 to your script to avoid a heading blank line.
for most of my scripts I use the settings in the following code piece of code:
dt=`sqlplus -s user/pwd@servicename < < EOF set feedback off set pagesize 0 set trimout on set trimspool on set linesize 300 set echo off set verify off select replace(to_char((sysdate-7),'YYYYMonDD')||'_' ||to_char((sysdate-1),'YYYYMonDD'),chr(10), '') from dual; exit; EOF` echo "test $dt$dt"
