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?

Improve this question
6
  • Try to add in sql this set sqlprompt ' ' (single quotes) Commented Apr 26, 2019 at 14:47
  • And also try to change <<-EOQ3 to <<EOQ3 Commented Apr 26, 2019 at 14:51
  • @RomeoNinov Thanks for suggestion. I tried both and no change - still have a white space. Commented 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 Commented Apr 26, 2019 at 15:15
  • 1
    @RomeoNinov SUCCESS! The answer is adding linesize 300 - apparently it was a wrapping issue. Thank you! Commented Apr 26, 2019 at 15:24

3 Answers 3

Reset to default
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')
Improve this answer
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:]')" Commented 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.

Improve this answer
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)

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.