error running sqlplus from shell file

Shell Programming and Scripting
1

I am running a shell file following script on bash shell in solaris 10

   \(
echo abc@orcl
echo abc
echo "set feedback off"
echo "truncate table SIndexDataTypeHst1_changes;"
\) | sqlplus -s

but getting the following error

ERROR:
ORA-01005: null password given; logon denied

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

user/password and service all are correct.

Can any body tell me where i am going wrong?

2

I always call sqlplus command in the following way and it has always worked.

login=username/password@tns_entry
sqlplus -s "$login" << EOF
@your_script.sql
OR
set head off
set verify off
select sysdate from dual;
EOF

Also, by seeing your error it is obvious that you are trying to connect as sys user without having the correct privileges to do so.

As far as possible, avoid connecting as sys user in scripts. Try using system user instead.

HTH, :cool:

Regards,

Praveen

3

Thanks for the reply.

but i want to find the problem in the script mentioned in my question.

Moreover, i am not trying to connect as sysdba.

4

Munir,

The problem in your script is twofold.

  1. Through a script, sqlplus CANNOT accept the password, if supplied independently of the username. It has to supplied with the username using the forward slash ("/").

eg: The below code does NOT work:

$ (
> echo abc@orcl
> echo abc
> echo "set head off"
> echo "select sysdate from dual;"
> ) | sqlplus -s

while the below code WORKS:

$ (
> echo abc/abc@orcl
> echo "set head off"
> echo "select sysdate from dual;"
> ) | sqlplus -s
  1. Since sqlplus is unable to parse the input (because of point 1 above), it is throwing both TNS error as well as the login error.

The below code should work and is as per your requirements (although I prefer the method that I mentioned in my earlier reply :)):

$ (
> echo "abc/abc@sid"
> echo "set feed off"
> echo "set verify off"
> echo "set head off"
> echo "select sysdate from dual;"
> ) | sqlplus -s

06-FEB-09
$

HTH, :cool:

Regards,

Praveen