Execute SQL query on Oracle working in Unix prompt but not in shell script

Question

I am trying to connect to an Oracle db using a ksh script. When I run this directly from the prompt, it works. But when I put it inside a script (abc.sh) it fails. Below is what I've put in the script (edited to make it shorter):

Here abc is the username, while abc$123 is the password of the user which has access on database DBNAME.

 #!/usr/bin/ksh

 sqlplus -s /nolog << EOF > output
 connect abc/abc$123@DBNAME;

 set echo off
 set heading off

 select table_name from dba_tables;
 exit;
 EOF

This works if typed directly, but run as ./abc.sh, gives error -

 ERROR ORA-01017: invalid username/password; logon denied

I'm sure I'm missing something simple, but can't figure this out. Thanks for your help.

Dale Myers · Accepted Answer · 2013-11-01 10:18:54Z

2

You cannot just write code like this and expect it to be passed to the relevant program. Your SQL lines are being executed by KSH instead of your SQL server. Instead you need to do something more along the lines of:

echo "SQL CODE HERE" | sqlplus ....

answered Nov 1, 2013 at 10:18

Dale Myers

2,8214 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xcentaur Over a year ago

Thanks a lot for this! I changed it to read: sqlplus -s /nolog @abc.sql where abc.sql had the connect and select queries. Working great now.

Abhijit Buchake Over a year ago

Thanks Velox. It helped me too. I was searching for this for past 1 hour.

Collectives™ on Stack Overflow

Execute SQL query on Oracle working in Unix prompt but not in shell script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related