How to search and replace in ksh within a heredoc variable once already set?

Question

I don't have extensive experience in ksh and am having a rough time with a multi-line variable in a script I'm trying to revise. Basically, I'm looking to strip out the first part of the resulting output up until some "CAMP" text and keep whatever is after that. The result gets sent in an email. What I've tried doesn't seem to work though.

Here's the multi-line part. Variables are set prior. I've removed the exact SQL.

   $ORACLE_HOME/bin/sqlplus -s << EndOfSql > ${LOG_FILE}
   $USERPASS@$ORACLE_SID
   SQL goes here with some other SET stuff
/
   EXIT;
EndOfSql

This is then eventually used like this to send the email to the email address in MAIL_ID:

   mailx -s "Email subject line"\
       $MAIL_ID < $LOG_FILE;

As mentioned, what I'm trying to do is strip out everything in the result up until some "CAMP" text in the heredoc result from the SQL output. I've tried things like this, but am having a tough time understanding heredoc and variables since there are so many methods out there:

CNTNT=${LOG_FILE#*CAMP}

  mailx -s "Email subject line"\
      $MAIL_ID < $CNTNT;

Any recommendations or resources you could please point me to? It'd be sincerely appreciated.

Answer 1

As mentioned in my previous comment, it looks like you're trying to capture output into a variable, but are actually sending it into a file.

As an alternative, this might work:

# store the entire query in a function
sql_query() {
$ORACLE_HOME/bin/sqlplus -s << EndOfSql
$USERPASS@$ORACLE_SID
SQL goes here with some other SET stuff
/
EXIT;
EndOfSql
}

# capture function output
DATA=$( sql_query )

# Discard unwanted data
echo "${DATA#*CAMP}" | 
    mailx -s "Email subject line" ${MAIL_ID:?}

Answer 2

The result of the sql is stored in a file. The filename is given by $LOGFILE.
After running the SQL the value of the variable LOGFILE will be still the same as before something like /home/user7347759/camp.out.
When you want to use the ${..#..} construction, you first have to fill another variable with the contents of the file. After parsing the output the reslt is stored in a variable. When you use <, the shell is expecting a filename. So you will need to store the results in a file.

# Demonstrate what you tried
filteredfile=/tmp/user7347759.tmp
varwithlogging="$(cat "${LOG_FILE}")"
CNTNT="${varwithlogging#*CAMP}"
echo "${CNTNT}"> "${filteredfile}"
mailx -s "Email subject line"\
      $MAIL_ID < "${filteredfile}"
rm -f "${filteredfile}"

You can do without the filteredfile when you use the process substition <(echo "${CNTNT}") or you can use <<< "${CNTNT}".
Your script will be shortened to

# Improved demonstration of what you tried
varwithlogging="$(cat "${LOG_FILE}")"
CNTNT="${varwithlogging#*CAMP}"
mailx -s "Email subject line"\
      $MAIL_ID <<< "${varwithlogging#*CAMP}"

The next thing you want is to skip to two temporary vars varwithloggingen CNTNT. Let's try something.

mailx -s "Email subject line"\
      $MAIL_ID <<< "$(sed 's/.*CAMP//' "${LOGFILE}")"

This would work nice when the LOGFILE only had one long line. When you have a multi-line LOGFILE, it will become trickier (remove all lines before the line with CAMP and remove part with *CAMP from the remaining first line).
When you can change the SQL script you can choose for something like @Henk suggested.