How to use sqlplus with a heredoc and file substitution args

Question

So, I am trying to apply some default configurations to every invocation of sqlplus by wrapping the script to be executed in a "header" and "footer" so that I get consistent execution.

However, as I implement this bash function, I realize that some of the scripts have parameters and, when I try to pass them to sqlplus when using a heredoc for the "wrapping", sqlplus gets confused and thinks the params meant for the substitution are actual for it (sqlplus).

So, short of creating a temporary file, is there a way to still use a heredoc (or any stdin stream like envsubst) along with script params?

code below (either source it in a file or simply copy&paste the function into your interactive shell):

function exec_sql_file(){
  local sql_file=${1:?You must supply and sql_file file to execute}
  local script_dir username password
  script_dir=$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
  source "${script_dir}/omc.env"

  local script="${script_dir}/sql/${sql_file}"

  sqlplus -s "$username/$password@sredb1_high" <<-EOF "${@:2}" | tail -n "+$#"
        set heading off;
        set feedback off;
        set linesize 10000;
        set newpage NONE;
        set colsep '|'
        $(cat "${script}")
        exit;
        EOF

} && export -f exec_sql_file

example.sql:

select &1 + &2 from dual

intended usage:

exec_sql_file queries/example.sql 1 3
         4

What is need is the "${@:2}" after the heredoc

the nominal cli it's trying to execute is:

sqlplus -s "$username/$password@sredb1_high" @sql/queries/example.sql 1 3

here is a working functional example with a temp file (not my preference)

function exec_sql_file(){
  local sql_file=${1:?You must supply and sql_file file to execute}
  local script_dir username password
  script_dir=$(cd -- "$(dirname -- "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
  local env_file="${script_dir}/omc.env"

  source "${script_dir}/omc.env"

  local script="${script_dir}/sql/${sql_file}"

  local tmpFile
  tmpFile=$(mktemp)
  cat <<-EOF > "${tmpFile}"
        set heading off;
        set feedback off;
        set linesize 10000;
        set newpage NONE;
        set colsep '|'
        $(cat "${script}")
        exit;
        EOF
  echo exit | sqlplus -s "$username/$password@sredb1_high" "@${tmpFile}" "${@:2}" |
    tail -n "+$#" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//'
} && export -f exec_sql_file

Answer 1

A heredoc changes where stdin is directed from. It does not act as or turn into a command-line argument, so the idea of putting an argument after a heredoc (or before a heredoc, for that matter) does not make any sense: heredocs do not participate in command-line argument ordering.

I suspect you may want something like:

buildArgs() {
  printf '%s\n' \
    'set heading off;' \
    'set feedback off;' \
    'set linesize 10000;' \
    'set newpage NONE;' \
    "set colsep '|'" \
  cat -- "$script"
  printf '%s\n' \
    'exit'
}


sqlplus -s "$username/$password@sredb1_high" "@"<(buildArgs) "${@:2}" |
  tail -n "+$#" |
  sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//'

...where "@"<(buildArgs) is (on Linux -- this differs across platforms) replaced by the shell with something like @/dev/fd/63, where /dev/fd/63 is a filename from which the output of buildArgs can be read.

(Note that this is a FIFO, so it can only be read once; if you need it to be seekable, this calls for a separate solution; zsh has built-in syntax in the form of =(buildArgs), but this feature is not to my knowledge currently available in bash).

Answer 2

I you run from a Linux type shell. To use variables in your heredoc, just set your variable in the shell and put your variable in the heredoc. The shell will expand the variable before sending the data to sqlplus eg

GRP_NAME="2024-03-01"

sqlplus -s /nolog <<EOF
whenever sqlerror exit 1
conn / as sysdba
SET DEFINE ON
PROMPT DROP RESTORE POINT ${GRP_NAME}
drop restore point ${GRP_NAME};
exit
EOF