CODEWITHSUNDEEP
oracle-databaseshellvariablessqlpluscarriage-return
SEUNGJAE MOON
SEUNGJAE MOON

Reputation: 1

How to input Oracle query statements in Unix shell variables

I am trying to input an Oracle query statement into a variable in a Unix shell.

I want to remove the header of the SQL result value, so I want to include "set heading off" in the QUERY variable, but I am curious about the exact syntax.

When I run it like below

#!/usr/bin
DB_CONN="sqlplus -s user/pass"

QUERY=
{
  set heading off
  select sysdate from dual;
}

RESULT=echo $QUERY | $DB_CONN

echo $RESULT

The result is as follows:

# syntax error at line 5 :'from' unexpected

Upvotes: 0

Views: 60

Answers (2)

dash-o
dash-o

Reputation: 14493

Bash offers multiple options to piping context into processes. In addition to pipe, "here document", you can use "here string".

RESULT=($DB_CONN <<< "$QUERY")

Also, for the case that your output is single row (select sysdate ...) you can use the bash read to parse the output. This has the advantage that it can parse multiple values if values are shell tokens

read sysdate v2 v3 <<< "($DB_CONN <<< "$QUERY")"

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

There are multiple syntax errors in your code. Read some documentation about Bash Sell!

I would suggest HERE document:

DB_CONN="sqlplus -s user/pass"

RESULT=$( $DB_CONN <<END_SQL
  set heading off
  select sysdate from dual;
END_SQL
)

echo $RESULT

13.12.24

Do not put any spaces before END_SQL, it must be the begin of the line.

But it also works with a variable:

QUERY="set heading off
  select sysdate from dual;"

echo "$QUERY" | $DB_CONN

Should return the same result.

Upvotes: 0

Related Questions