Visit Our UNIX and Linux User Community


error running sqlplus from shell file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting error running sqlplus from shell file
# 1  
Old 02-06-2009
error running sqlplus from shell file

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  
Old 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, Smilie

Regards,

Praveen
# 3  
Old 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.
# 4  
Old 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 Smilie):

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, Smilie

Regards,

Praveen

Previous Thread | Next Thread
Test Your Knowledge in Computers #12
Difficulty: Easy
CPU stands for 'Computer Processing Unit' and controls all of a computer's functions, acting as the brain(s) of computers.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

2. Windows & DOS: Issues & Discussions

Help required for Running SQLPLUS command from Bat file

Hello All, Good Afternoon. I am new to this platform and I need one small help regarding running a SQL file from Bat file. Below is what I am doing, 1. I placed the below command in one Bat file. start putty.exe -ssh user@host -pw pwd -m C:\2.txt 2. In 2.txt, I have below command. ... (3 Replies)
Discussion started by: PavanPatil
3 Replies

3. Solaris

Error during running sqlplus command from shell script in Solaris

I am using following code to connect to oracle database from solaris shell script. which will try thrice to connect the database ...at the 4rth atempt it will exir=t. count=0 while ; do sqlplus -s $usrname/$password@dbSID <<-EOF | tee $logfile WHENEVER OSERROR EXIT 9; WHENEVER SQLERROR... (4 Replies)
Discussion started by: millan
4 Replies

4. Shell Programming and Scripting

Running sqlplus for 5 DB in a shell

Hi all, on AIX 6.1 I want to run the following but for 5 DB. How should I do that ? Using FOR, WHILE ???? How ? export ORACLE_SID=DB1 sqlplus / as sysdba << EOF whenever sqlerror exit sql.sqlcode; whenever oserror exit FAILURE set define off set head off set feedback off set echo off... (1 Reply)
Discussion started by: big123456
1 Replies

5. Shell Programming and Scripting

Error while using sqlplus command inside 'if' condition in an unix shell script

Hi all, I am using the below given sqlplus command in my unix script to invoke a stored procedure which returns a value .It works fine. RET_CODE=$(/opt/oracle/product/10.2.0.4.CL/bin/sqlplus -S $USER/$PASSWD@$DB_NAME <<EOF EXEC MY_PKG.MY_SP (:COUNT); PRINT COUNT; commit; ... (6 Replies)
Discussion started by: Shri123
6 Replies

6. Shell Programming and Scripting

URGENT: cron job not running the sqlplus command in shell script

cron job not running the sqlplus command in shell script but the shell script works fine from command line.. Cronjob: 5 * * * * /home/dreg/script.sh script.sh: #!/bin/ksh /oracle/u000/app/oracle/product/10204/GEN/bin/sqlplus -s <user>/<pass>@<sid/home/dreg/sqlscript.sh ... (18 Replies)
Discussion started by: Ikea
18 Replies

7. Shell Programming and Scripting

sqlplus error output to different error log file

HELLO, I am using such a command to write oracle sqlplus query result to text file: sqlplus -S xxx/xxx@xxxxxxx @\tmp\2.sql>\tmp\123.txt Is it possible to script that: If command succesfull write in \tmp\log.txt: timestamp and "succeded" and create 123.txt with results else If error... (2 Replies)
Discussion started by: tomasba
2 Replies

8. Shell Programming and Scripting

error in passing a variable to sqlplus from a shell script

hi, I am using a shell script from where i will be conecting to sqlplus.. i am having a problem in passing a variable to sqlplus query.. i will be assigning the variable in the unix environment..whenever i am trying to pass a variable having the contents greater than 2500 characters, i am... (3 Replies)
Discussion started by: kripssmart
3 Replies

9. Shell Programming and Scripting

To pass the .sql file as a paramter to sqlplus through shell programming

Hi, Currently i have a .sql file 1.sql. I need to pass that as a parameter through a shell script to the sqlplus inside the same shell script. How I should I do.can anyone help me pls. I have an req where I need to send the .sql file and the place where the script has to create a .csv... (9 Replies)
Discussion started by: Hemamalini
9 Replies

10. Shell Programming and Scripting

running shell script from sqlplus

I have a script which connects to different database servers using sqlplus. Is there a way by which I can run a shell command on that host from sqlplus? I know about 'host' command but it runs script on the local machine where the original script is running. Is there a way to run command on the... (9 Replies)
Discussion started by: dkr123
9 Replies

Featured Tech Videos