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}.
    – Kusalananda
    May 19, 2018 at 6:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .