sql output into script variables ....?

[Premalm]

Hi Guys,

RUNNINGSQL="select jobpid, job_id from jobreq where status = 'COMPLETED' AND JOB_ID = 257;"

runjobs()
{
######################################################
sqlplus -s $UID/$PWD@dev |&
print -p "set head off"
print -p "set feedback off"
print -p $RUNNINGSQL # Run a query to get all the jobs that are running
print -p "exit"
while read -p PID JOBID
do
echo $PID
echo $JOBID
done
######################################################
}

how do I get PID and jobpid in the variables ? The above loop doesn't work.

Thanks
Premal

 

[PHV]

Take a look here: faq822-2218

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[stefanwagner]

I guess your command

RUNNINGSQL="SELECT ..."
runjobs()
{
sqlplus -s $UID/$PWD@dev |&
print -p "set head off"
print -p "set feedback off"
print -p $RUNNINGSQL
print -p "exit"
}

returns something like "511 257".

QUERY_RESULT=$(runjobs)
#echo $QUERY_RESULT
PID=$(echo $QUERY_RESULT | sed 's/[ \t].*//g')
JOB_ID=$(echo $QUERY_RESULT | sed 's/.*[ \t]//g')
#
echo "PID = $PID	JOB_ID = $JOB_ID"

If it doesn't work, I assume, you get more than one line of result?

Then awk could be a good tool for you...

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung