db connection

Shell Programming and Scripting
1

Hi,
I'm trying to write a script which will run a select on db.

#!/usr/bin/ksh
SQL_INPUT_FILE=file_name.sql
SQL_OUTPUT_FILE=output.txt
MANPATH=/usr/share/man:/opt/SUNWspro/man:/usr/local/man
PATH=/opt/oracle/product/11g/bin:/sbin:/usr/sbin:/usr/ccs/bin:/usr/bin:/sbin:/opt/oracle/product/11g/OPatch:/usr/local/bin:/u
sr/bin:/usr/X11R6/bin:/bin:/usr/games:/opt/gnome/bin:/opt/kde3/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin:/bin
NLS_LANG=AMERICAN_AMERICA.UTF8
ORACLE_SID=ora11g
ORACLE_TERM=xterm
ORACLE_HOME=/home/oracle/product/11g
export ORACLE_SID
export NLS_LANG
export ORACLE_HOME
export PATH
export ORACLE_TERM

/home/oracle/product/11g/bin/sqlplus user/pass@ora11g < $SQL_INPUT_FILE  > $SQL_OUTPUT_FILE

however the output file turns out such that:

SQL*Plus: Release 11.1.0.7.0 - Production on Sun May 29 23:43:14 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


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

Can you help me?

2

First try

tnsping ora11g

It this works it should work for sqlplus also. Otherwise listener might not be running. If no problem in listener. Are you sure you are giving $user/$pass rather than user/pass in the sqlplus?

3

If tnsping does not work also check the local tnsnames.ora file if there is an entry for ora11g. Usually the file can be found at $ORACLE_HOME/network/admin.