2

I have an Oracle database table that is storing the full path and name of a file in a table column. In a SQL client I can run:

select file_name from table where request_id=12345

and I will get this result:

/home/example_path/example_file_name_1.xls

In a bash script I have this statement (which I largely copied from another example I'm working from):

# Get Actual Output File Name
VREQID=12345
ATTACH_FILE=$(sqlplus -s $ORA_USER_PSSWD <<-EOQ3
set heading off 
set feedback off 
set pagesize 0 
set sqlprompt "" 
set verify off 
set pause off
select file_name from table where request_id=($VREQID)
/
EOQ3
)
echo 'ATTACH_FILE: ' "$ATTACH_FILE"
# Script continues...

My echo statement is then showing:

/home/example_path/example_file_name_ 1.xls

There's a space between the underscore and the 1. I've tried using quotes to prevent this but it didn't help.

What is causing this and how do I prevent this?

6
  • Try to add in sql this set sqlprompt ' ' (single quotes) Apr 26, 2019 at 14:47
  • And also try to change <<-EOQ3 to <<EOQ3 Apr 26, 2019 at 14:51
  • @RomeoNinov Thanks for suggestion. I tried both and no change - still have a white space. Apr 26, 2019 at 15:11
  • Try also to add this: set linesize 300, maybe sqlplus wrap somehow the output. And also column file_name format a50 may help Apr 26, 2019 at 15:15
  • 1
    @RomeoNinov SUCCESS! The answer is adding linesize 300 - apparently it was a wrapping issue. Thank you! Apr 26, 2019 at 15:24

3 Answers 3

0

I imagine something to do with your SQL query is causing that space because I don't see any bash/shell script related mechanism causing it. I'm no SQL expert so I can't help there but you have a few shell/bash methods of removing it:

  1. Parameter expansion
    If you call the parameter like so it will remove any spaces: "${ATTACH_FILE// /}"
$ ATTACH_FILE='/home/example_path/example_file_name_ 1.xls'
$ echo "${ATTACH_FILE// /}"
/home/example_path/example_file_name_1.xls 
ATTACH_FILE="${ATTACH_FILE// /}"
  1. Using tr
    You can use tr to delete spaces
ATTACH_FILE=$(echo "$ATTACH_FILE" | tr -d ' ')
  1. Using sed
    You can also use sed to delete spaces
ATTACH_FILE=$(echo "$ATTACH_FILE" | sed 's/ //g')
1
  • 1
    Thank you very much. I have added the following to the script and it is effectively removing the whitespace. It works but still curious if anyone knows the root cause. ATTACH_FILE_NO_WHITESPACE="$(echo -e "${ATTACH_FILE}" | tr -d '[:space:]')" Apr 26, 2019 at 14:22
0

Per the comments of @RomeoNinov - this was a linesize issue. The answer was to add

 linesize 300 

before running the sqlplus query.

0

To avoid string/word wrap you can set the width of the output to big value like 300 symbols:

set linesize 300

Also for particular column you can define specific format like:

column file_name format a50

(50 alphanumeric symbols)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .