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

-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
    There is absolutely no difference between $variable and ${variable}. – Kusalananda May 19 '18 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.