0

I have this shell script, which login to oracle database, select from a table and put the result on other variable

Username=User1
Password=Pass1
eval DatabaseName=db_tst

var1 = 1

Result=`sqlplus -silent $Username/$Password@$DatabaseName <<EOF 
whenever sqlerror exit sql.sqlcode
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF 
select to_char(RUN_DATE,'YYYY-MM-DD:hh24:mi:ss') from table1 where col1 = $var1;
EXIT;
EOF`

echo $Result

the problem is the variables that passed to the "Result" ($Username, $Password...etc ) it's values not recognized. it gave me this message:

ERROR: ORA-01017: invalid username/password; logon denied SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

ERROR: The SQL Plus Command Failed.

And when write it's values manually, it's loged in and the Result variable get the needed value. However I don't want to write the values manually, it should be passed through variables

1 Answer 1

-2

try like this...

Result=`sqlplus -silent ${Username}/${Password}@$DatabaseName <<EOF 
whenever sqlerror exit sql.sqlcode
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF 
select to_char(RUN_DATE,'YYYY-MM-DD:hh24:mi:ss') from table1 where col1 = ${var1};
EXIT;
EOF`

Result=$(sqlplus -silent ${Username}/${Password}@$DatabaseName <<EOF 
whenever sqlerror exit sql.sqlcode
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF 
select to_char(RUN_DATE,'YYYY-MM-DD:hh24:mi:ss') from table1 where col1 = ${var1};
EXIT;
EOF)
1
  • 1
    There is absolutely no difference between $variable and ${variable}. Commented May 19, 2018 at 6:51

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.