Question: Can you provide me a shell script that will accept oracle credentials, sql query to be executed and displays the output?
Answer: The shell script given below prompts some basic information and displays the output of the SQL.
You can use the same concept and hard-code some of these values in the shell-script itself and even run this script in the background to generate the output of an oracle sql query automatically (or use the oracle shell script from cron job).
This script accepts following values from the user:
- Oracle username
- Oracle password
- Oracle SQL Query to be executed.
Script validates the $ORACLE_HOME and $ORACLE_SID set in environment.
Shell Script to Execute Query in Oracle
This script allows you to enter the credential to login to oracle, and executes the query and displays the output.
$ cat sql_query.sh
#!/bin/bash
# Validate the value of ORACLE_HOME #
# If ORACLE_HOME is empty #
if [ -z $ORACLE_HOME ]
then
echo "Set the ORACLE_HOME variable"
exit 1
fi
# If ORACLE_HOME doesn't exist #
if [ ! -d $ORACLE_HOME ]
then
echo "The ORACLE_HOME $ORACLE_HOME does not exist"
exit 1
fi
# Validate the value of ORACLE_SID #
if [ -z $ORACLE_SID ]
then
echo "Set the ORACLE_SID variable"
exit 1
fi
sid_dir=`echo $ORACLE_HOME | sed -n 's@^\(\/[^\/]\+\/\).*$@\1@;p'`
# Check the given ORACLE_SID is valid.
if [ ! -d $sid_dir/oradata/$ORACLE_SID ]
then
echo "The ORACLE_SID is invalid"
exit 1
fi
# Enter the username and password to login to oracle #
echo "Enter the username"
read username
echo "Enter password"
stty -echo
read password
stty echo
# Get the query , no validation applied for query #
echo "Enter the query"
read query
# Login and execute the query.
echo "set feedback off verify off heading off pagesize 0
$query
exit" | $ORACLE_HOME/bin/sqlplus -s $username/$password | while read output ;
do
echo $output
done
In database,
SQL> select * from test;
SNO
----------
12
23
34
45
$ ./sql_query.sh Enter the username system Enter password Enter the query select SNO from test; 12 23 34 45
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
This is not working on AIX…
This is not working ….showing the ORACLE_SID is invalid
I guess (sorry if this seems to be too obvious) we need to have Oracle_home, meaning have Oracle in your Unix/AIX/Linux environment.
Can you please elaborate a little bit on this with some advice on this regards?
Thanks,
Oscar.
Also, would you guide me on “How to” extract informatino from Cron itself?
I need to get the data to know exactly which processes are schedule to run in a particular day and/or which scripts are already run *(from Cron) at a given time of the day and which ones are pending for that same day.
Appreciate your kind support,
Oscar.
There is a big security with this script; it exposes the user’s Oracle ID and password to other users that might invoke the ps -ef command while the query is running. Although many queries are quick, some are not. You can fix the above example by replacing the “-s $username/$password ” parm with “/nolog” and adding the following line right after the set statement:
connect $username/$password
Hi,
This is good
But some OS it is not working
Present Now I am using HP Server but host is not working through a server
Could you please let me know any suggestion for the below?
its in ksh
I am getting below error
Code:
SELECT ‘ALTER DISKGROUP ‘ $DG ‘ DROP FILE ”’||R.NAME||”’; ‘
*
ERROR at line 1:
ORA-00911: invalid character
Tried different combinations like ${DG}, $DG, “${DG}”.
Function :-
Code:
function DELNWE5O {
export ORACLE_HOME=/ora00/app/oracle/product/11.2.0/grid_1
export ORACLE_SID=+ASM
export PATH=${ORACLE_HOME}/bin:${PATH}
DG=`sqlplus -s ‘/as sysasm’ < /ora00/grid/Mani/dg.log
export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=nwe5o
export PATH=${ORACLE_HOME}/bin:${PATH}
LIBPATH=${ORACLE_HOME}/lib
sqlplus -s ‘/as sysdba’ <<-"EOF"
SET PAGES 0 LINES 175 HEAD OFF FEED OFF
spool /ora00/grid/Mani/delete_list_ods_nwe5o.sql
SELECT 'ALTER DISKGROUP ${DG} DROP FILE '''||R.NAME||'''; '
FROM DBA_REGISTERED_ARCHIVED_LOG R, DBA_CAPTURE C, V$ASM_FILE F
WHERE R.CONSUMER_NAME = C.CAPTURE_NAME
AND R.NEXT_SCN < C.REQUIRED_CHECKPOINT_SCN
AND F.FILE_NUMBER = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,1)+1,INSTR(R.NAME,'.',1,2)-INSTR(R.NAME,'.',1,1)-1)
AND F.INCARNATION = SUBSTR(R.NAME,INSTR(R.NAME,'.',1,2)+1);
spool off
EXIT
EOF
}
Thanks
can you explain what is done here.
sid_dir=`echo $ORACLE_HOME | sed -n ‘s@^\(\/[^\/]\+\/\).*$@\1@;p’`