Execute SQL query in unix script

Shell Programming and Scripting
1

Hi

I am new in unix. oracle and unix are installed in my sytem.i need the script
which could connect to the oracle using username ,password and schema and can run the select * from tab query.

Thanks
vijay

2

Here's one example.

ORA_SID=mydb
DBUSER=mylogin

#
# not a fan of clear text passwords in files at all, but for this example will use it.
# It is better than sqlplus username/pwd@db though.  At lease others can't
# see the passwd using "ps -ef | grep sqlplus".
#
DBPWD=mypwd

sqlplus -s /nolog <<-EOF

   connect $DBUSER/$DBPWD@$ORA_SID

   select 'TEST' from dual;

   exit

EOF

I have not tested this, but it should work.

Good luck.

3

Thanks for your reply i executed that script but in bash shell there is no manual
entry for sqlplus due to this script is not executing...

but i got the idea how do we connect to oracle

Thanks

4

Not sure what you mean by "manual entry for sqlplus", but if the value of ORACLE_HOME is set then that script should run successfully.

tyler_durden

5

i checked ORACLE_HOME is not set.....any idea how do we set it

Thanks
Vijay Sahu

6

Check with your SA, or give a search for sqlplus

find / -name "sqlplus" 2>/dev/null

sqlplus will be resides at $ORACLE_HOME/bin/sqlplus , which you can find from the output of the above command and then set the ORACLE_HOME and add the same in PATH variable.

I assume you are using OS id, which is in the correct group to access the oracle directory.

7

ran the command u mentioned and also command find / -name "sqlplus" -print
but no result is coming up ...even i checked the PATH variable using set and env commands ...no such variable $ORACLE/bin/sqlplus defined...

i tried to set this variable by command
PATH=$ORACLE/bin/sqlplus or set PATH=$ORACLE/bin/sqlplus either ways

But these both commands are not working

anyways but you have given me me proper picture that what should be and what should not be

thanks for your quick response
Vijaysahu

8

Seems

  • oracle is not installed or,
  • id you are using maynot be in the correct group

Please check with your SA

9

Hi vijay,
Please check are you create oracle group or not . If yes then you switch to oracle user and then execute sqlplus script.
Regards,
Mohanty