How to Get SQLPLUS Query Output to a Shell Script Variable

Most of the time during the shell scripting we get requirement to get the database table query result values to a shell script variables. Here is one example to get a single column values to a variable. If you have multiple column outputs then we will have to concatenate into a single column and pass it to the shell script variable. You can use awk or scripting commands to make it separate.

Example: In this example I am counting the data pump jobs into a shell variable called IMP_ACTIVE.
IMP_ACTIVE=`sqlplus -s scott/tiger << !!
 set pages 0 line 130 serveroutput on feedback off
 select count(*) from dba_datapump_jobs where job_name='AUD' and state='EXECUTING';
 exit;
 !!`
 N1COUNT=`sqlplus -s / << !!
 set pages 0 line 130 serveroutput on feedback off
 $N1SQL
 exit;
 !!`
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google