Need help in executing the SQL via shell script and use the result set.

Question

Hi All,

I currently have a request to build a shell script to get some data from the table using SQL(ORACLE). The query which I'm running return n number of rows.. Is there a way to use something like result set.

Currently , I'm re-directing it to a file, but I'm not able to reuse the data again for the further processing..

Any help on this would be appreciated...

Regards, Senny

Answer 1

Your question is pretty short on specifics (a sample of the file to which you've redirected your query output would be helpful, as well as some idea of what you actually want to do with the data), but as a general approach, once you have your query results in a file, why not use the power of your scripting language of choice (ruby and perl are both good choices) to parse the file and act on each row?

Answer 2

Thanks for the reply Gene. The result file looks like:

UNIX_PID  37165
----------
PARTNER_ID  prad
--------------------------------------------------------------------------------
XML_FILE
--------------------------------------------------------------------------------
/mnt/publish/gbl/backup/pradeep1/27241-20090722/kumarelec2.xml


pradeep1
/mnt/soar_publish/gbl/backup/pradeep1/11089-20090723/dataonly.xml


UNIX_PID 27654
----------
PARTNER_ID  swam
--------------------------------------------------------------------------------
XML_FILE
--------------------------------------------------------------------------------
smariswam2
/mnt/publish/gbl/backup/smariswam2/10235-20090929/swam2.xml

There are multiple rows like this. My requirement is only to use shell script and write this program.

I need to take each of the pid and check if the process is running, which I can take care of.

My question is how do I check for each PID so I can loop and get corresponding partner_id and the xml_file name? Since it is a file, how can I get the exact corresponding values?

I hope this info helps.

Regards, Senny

Answer 3

Here is one suggested approach. It wasn't clear from the sample you posted, so I am assuming that this is actually what your sample file looks like:

UNIX_PID 37165 PARTNER_ID prad XML_FILE /mnt/publish/gbl/backup/pradeep1/27241-20090722/kumarelec2.xml pradeep1 /mnt/soar_publish/gbl/backup/pradeep1/11089-20090723/dataonly.xml
UNIX_PID 27654 PARTNER_ID swam XML_FILE smariswam2 /mnt/publish/gbl/backup/smariswam2/10235-20090929/swam2.xml

I am also assuming that:

1. There is a line-feed at the end of the last line of your file.
2. The columns are separated by a single space.

Here is a suggested bash script (not optimal, I'm sure, but functional):

#! /bin/bash
cat myOutputData.txt |
while read line;
do
    myPID=`echo $line | awk '{print $2}'`
    isRunning=`ps -p $myPID | grep $myPID` 
    if [ -n "$isRunning" ]
    then
     echo "PARTNER_ID `echo $line | awk '{print $4}'`"
     echo "XML_FILE `echo $line | awk '{print $6}'`"
    fi
done

The script iterates through every line (row) of the input file. It uses awk to extract column 2 (the PID), and then does a check (using ps -p) to see if the process is running. If it is, it uses awk again to pull out and echo two fields from the file (PARTNER ID and XML FILE). You should be able to adapt the script further to suit your needs. Read up on awk if you want to use different column delimiters or do additional text processing.

Things get a little more tricky if the output file contains one row for each data element (as you indicated). A good approach here is to use a simple state mechanism within the script and "remember" whether or not the most recently seen PID is running. If it is, then any data elements that appear before the next PID should be printed out. Here is a commented script to do just that with a file of the format you provided. Note that you must have a line-feed at the end of the last line of input data or the last line will be dropped.

#! /bin/bash
cat myOutputData.txt |
while read line;
do
    # Extract the first (myKey) and second (myValue) words from the input line
    myKey=`echo $line | awk '{print $1}'`
    myValue=`echo $line | awk '{print $2}'`

    # Take action based on the type of line this is
    case "$myKey" in
     "UNIX_PID")
      # Determine whether the specified PID is running
      isRunning=`ps -p $myValue | grep $myValue` 
     ;;
     "PARTNER_ID")
      # Print the specified partner ID if the PID is running
      if [ -n "$isRunning" ]
      then
       echo "PARTNER_ID $myValue"
      fi
     ;;
     *)
      # Check to see if this line represents a file name, and print it
      # if the PID is running
      inputLineLength=${#line}
      if (( $inputLineLength > 0 )) && [ "$line" != "XML_FILE" ] && [ -n "$isRunning" ]
      then
       isHyphens=`expr "$line" : -`
       if [ "$isHyphens" -ne "1" ]
       then
         echo "XML_FILE $line"
       fi
      fi
     ;;
    esac
done

I think that we are well into custom software development territory now so I will leave it at that. You should have enough here to customize the script to your liking. Good luck!

Answer 4

This looks good.. but the result file does not have all the values in single line... Is there a way in shell script where I can get the result of the query in one variable and use it like result set.