Bash Pass local file with params to remote server

Question

How can I run a file and pass variables to a file that is local over to a remote server?

When I run the code below, with redirecting the file_in without the <, it does work.

This works, but the file has to be on the remote server: ./remoteserver/xxxxx.sql

function Runsshlocal
{
  HOSTS=$1
  SID=$2
  file_in=$3
  shift 3
  SQL_PARAMS=$@
  output=$(
    echo "exit" | 
      ssh -q ${HOSTS} "ORAENV_ASK=NO;ORACLE_SID=${SID}; . oraenv -s;
        sqlplus -s \"/ as sysdba \"  @${file_in} ${SQL_PARAMS}" 
  )
  echo $output 
}

TEST=$(Runsshlocal ${TARGET_SERVER} ${TARGET_SID} /remoteserver/xxxxx.sql  ${PARAM1} ${PARAM2})
echo $TEST

But I would like to run it where the xxxxx.sql file is on the local server and not the remote server.

This does not work:

function Runsshlocal
{
  HOSTS=$1
  SID=$2
  file_in=$3
  shift 3
  SQL_PARAMS=$@
  output=$(
    echo exit | ssh -q ${HOSTS} "ORAENV_ASK=NO;ORACLE_SID=${SID}; . oraenv -s;sqlplus -s \"/ as   sysdba \" " <  @${file_in} ${SQL_PARAMS} 
  )
  echo $output }

  TEST=$(
    Runsshlocal ${TARGET_SERVER} ${TARGET_SID} /localserver/xxxxx.sql  ${PARAM1} ${PARAM2}
  )
  echo $TEST

Answer 1

Try this:

output=$(ssh -q ${HOSTS} "ORAENV_ASK=NO;ORACLE_SID=${SID}; . oraenv -s;sqlplus /dev/stdin -s \"/ as   sysdba \" " ${SQL_PARAMS} < "$file_in" )

This uses the SQL file as standard input to ssh. sqlplus will then read it as its own input.

Answer 2

Following up on Barmar's answer, try a heredoc with an embedded herestring:

output=$(ssh -q ${HOSTS} << _REMOTE_COMMANDS_
ORAENV_ASK=NO
ORACLE_SID=${SID}
. oraenv -s
# edit: using single quotes to surround SQL is probably a bad idea...
#                                                      v                 v
sqlplus /dev/stdin -s '/ as sysdba ' ${SQL_PARAMS} <<< "$(cat "$file_in")"
_REMOTE_COMMANDS_
)

Depending on the contents of the sql code, maybe embed a heredoc in the heredoc.

output=$(ssh -q ${HOSTS} << _REMOTE_COMMANDS_
ORAENV_ASK=NO
ORACLE_SID=${SID}
. oraenv -s
sqlplus /dev/stdin -s '/ as sysdba ' ${SQL_PARAMS} << '_END_SQL_'
$(cat "$file_in")
_END_SQL_
_REMOTE_COMMANDS_
)