error running sqlplus from shell file

mmunir · #1 (**permalink**) 02-06-2009

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?

sunpraveen · #2 (**permalink**) 02-06-2009

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

Code:

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,

Regards,

Praveen

mmunir · #3 (**permalink**) 02-06-2009

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.

sunpraveen · #4 (**permalink**) 02-06-2009

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:

Code:

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

while the below code WORKS:

Code:

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

2. 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

):

Code:

$ (
> 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,

Regards,

Praveen

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
error in passing a variable to sqlplus from a shell script	kripssmart	Shell Programming and Scripting	3	07-08-2008 08:01 PM
To pass the .sql file as a paramter to sqlplus through shell programming	Hemamalini	Shell Programming and Scripting	9	06-17-2008 07:34 AM
error in running shell script in cron	sari	Shell Programming and Scripting	3	04-16-2008 05:09 AM
running shell script from sqlplus	dkr123	Shell Programming and Scripting	9	07-20-2006 11:52 PM
Running SQLPLUS from Shellscripting ...	chittari	Shell Programming and Scripting	2	06-12-2006 05:09 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by