Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Passing sqlplus output to shell variable

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing sqlplus output to shell variable
# 1  
Old 03-21-2016
Passing sqlplus output to shell variable
Hi ,

I am using below code :

Code:
for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
ORACLE_SID=$i
export ORACLE_SID;
dest=`sqlplus  "/ as sysdba" <<EOF
set heading off feedback on verify off
select DESTINATION from v\\$archive_dest where target in ('REMOTE','STANDBY');
exit
EOF`
echo $dest;
done


Below is the output:

Code:
c456veq {/home/oracle}: ./pwdcopy.sh
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 06:23:01 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> no rows selected SQL> ora426b 1 row selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 06:23:01 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> no rows selected SQL> ora427b 1 row selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

I am looking for the red colored output only.

Best regards,
Vishal
# 2  
Old 03-21-2016
Hello admin_db,

Could you please try following and let me know if this helps.
Code:
./your_script.ksh | awk '{match($0,/rows selected SQL>.*row selected. SQL>/);if(substr($0,RSTART+19,RLENGTH-40)){print substr($0,RSTART+19,RLENGTH-40)}}'

Hope this helps.

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
admin_db
# 3  
Old 03-21-2016
My ORACLE is a bit rusty, but I'd think by setting a few options correctly you can get rid of most of the undesired output. Did you try setting feedback off?
# 4  
Old 03-21-2016
Most of the undesired output will be surpressed when you call sqlplus with the -s option.
Setting feedback off will get rid of the no rows selected message - btw. your output shows you executed 2 queries (one returning no rows, one returning the archive-destination) where your script only shows one.
Code:
for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
   ORACLE_SID=$i
   export ORACLE_SID;
   dest=`sqlplus -s "/ as sysdba" <<EOF
      set heading off feedback off verify off
      select DESTINATION from v\\$archive_dest where target in ('REMOTE','STANDBY');
      exit
EOF`
   echo $dest;
done

This User Gave Thanks to cero For This Post:
admin_db
# 5  
Old 03-21-2016
And, for the for loop, try
Code:
for i in $(ps -ef | awk '/pmon/ {split ($8, T, "_"); print T[3]}')

This User Gave Thanks to RudiC For This Post:
admin_db
# 6  
Old 03-22-2016
Thanks !

Now all is good and I want to copy password file of primary to standby -I am using something like below and looks like this is not working.

Code:
for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
   ORACLE_SID=$i
   export ORACLE_SID;
   dest=`sqlplus -s "/ as sysdba" <<EOF
      set heading off feedback off verify off
      select DESTINATION from v\\$archive_dest where target in ('REMOTE','STANDBY');
      exit
EOF`
echo $dest;
Ht=`tnsping $dest|sed -n 's/.*[Hh][Oo][Ss][Tt] *= *\([^)]*\).*/\1/p'`
echo "Hostname " $Ht;
scp -i ~/.ssh/dg_key $ORACLE_HOME/dbs/orapw"$ORACLE_SID"* oracle@"$Ht":$ORACLE_HOME/dbs/orapw"$dest"*
done

Best regards,
Vishal
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Passing variable as input & storing output in other variable

I have a below syntax its working fine... var12=$(ps -ef | grep apache | awk '{print $2,$4}') Im getting expected output as below: printf "%b\n" "${VAR12}" dell 123 dell 456 dell 457 Now I wrote a while loop.. the output of VAR12 should be passed as input parameters to while loop and results... (5 Replies)
Discussion started by: sam@sam
5 Replies

2. Shell Programming and Scripting

Passing a parameter from a shell script to sqlplus

Hi All, I'm new to Linux and scripting, apologies in advance for 'stupid' questions. Please help... Im writing a script that calls a sqlplus script but the sqlplus requires inputs and i cant seem to get this to work. here is my code. #!/bin/sh TERM=vt100 export TERM... (4 Replies)
Discussion started by: Mahomed
4 Replies

3. Shell Programming and Scripting

Sending sqlplus output to a shell variable

I am trying to import a sqlplus output into a shell variable but it doesnt seem to be working. set -x export DEPENDENT_CR_NO=`sqlplus -s /nolog <<EOF conn username/passwd set heading off select dependency from custom_patches where patch_name='PATCH.zip'; exit; EOF` echo $DEPENDENT_CR_NO ... (2 Replies)
Discussion started by: beginer314
2 Replies

4. Shell Programming and Scripting

Shell Script passing parameters to sqlplus code

Hello All, I am interested in finding out a way to pass parameters that are entered at the prompt from HP unix and passed to SQLPlus code with a Shell Script. Is this possible? Thanks (4 Replies)
Discussion started by: compprog11
4 Replies

5. Shell Programming and Scripting

Passing Shell array to SQLPlus

Dears, Newbie here and tried to search this topic for 3 days now with results. I have a shell array and I want to use it in sqlplus with one connection. here is what I have for now #!/bin/ksh FileName=1000 FileName=2000 FileName=3000 FileName=4000 sqlplus /nolog <<EOF connect... (20 Replies)
Discussion started by: roby2411
20 Replies

6. Shell Programming and Scripting

store sqlplus output in variable

hi how can i store sqlplus output to a variable in sh script (not bash) Thanks MM (1 Reply)
Discussion started by: murtymvvs
1 Replies

7. Shell Programming and Scripting

Passing the unix variable to sqlplus

Hi, I am writing a script which creates an external table using a shell script. My requirement is like this. Usage: . ./r.ksh <table_name> - this should create an external table. e.g . ./r.ksh abc - this should create an external table as abc_external. How do i achieve this? Please... (5 Replies)
Discussion started by: Anaramkris
5 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. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

10. Shell Programming and Scripting

passing parameters from a shell script to sqlplus

Hi , I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script Right now no file is generated on the unix box...it is a... (2 Replies)
Discussion started by: phani
2 Replies
Login or Register to Ask a Question