Shell Programming and Scripting

I am writing a ksh to check for duplicate records in two different set of tables on oracle database, to get this i am running two plsql qurries at a time through the ksh, so the output of the qurries will be stored in variable say "SQL_STRING". So now to say if duplicate records exists in table or not i have to write a script which checks the count of the word "no rows selected" in the SQL_STRING variable.

One of our friend on UNIX.COM helped me with the code below which counts from a file instead of from a variable

Code:

#!/bin/ksh
str='no rows selected'
num=$(awk '{n+=gsub(str,"")}END{print n}' str="${str}" mySampleFile.txt)
echo "Total number of [${str}] = ${num}"

For example the variable SQL_STRING has data as below:

no rows selected no rows selected
ora-1234 sql querry failed
no rows selected

Now we should look for the string "no rows selected" (note: it has spaces between) in the variable SQL_STRING and then store the count of the string in a variable.
for the above data the output should be 3 since the string "no rows selected" occurred three times.
Any help is appreciated.

Do not post classroom or homework problems in the main forums. Homework and coursework questions can only be posted in this forum under special homework rules.

Please review the rules, which you agreed to when you registered, if you have not already done so.

More-than-likely, posting homework in the main forums has resulting in a forum infraction. If you did not post homework, please explain the company you work for and the nature of the problem you are working on.

If you did post homework in the main forums, please review the guidelines for posting homework and repost.

Thank You.

The UNIX and Linux Forums.

This is not an home work, i am new to forums concept and to the unix too.
I have a situation at work where i need to compare two oracle tables and see wheatear we have duplicates or not. if duplicates exists then i should ftp those records to mainframe system and if no duplicates exists then should go to next step.
i have posted this question previously and got an answer to it, but once i started working on it i found some changes and then posted a new thread.

If the command you referenced is working, modulo the variable issue, then this will probably do the trick:

Code:

str="no rows selected"
num=$(echo "$SQL_STRING" | awk -v str="$str" '{ n += gsub (str, "" ); } END{ print n+0; }')
echo $num

Depending on your needs, you could also just pipe the output of the database query command directly into awk and not worry about any size limits that the shell might put on the contents of a variable.

The 'n+0' in the print ensures that you get a number out. If the query returns no output, n will not have been set and the print will print a blank rather than 0; the n+0 forces it to print a zero.

---------- Post updated at 20:07 ---------- Previous update was at 19:39 ----------

As was pointed out in another post just a few minutes ago, if the SQL_STRING is empty, echo will still print a single newline so n should always be set. Caution never hurts though.

Thank you very much

Thanks for the explanation too

You are most welcome. I believe that it is most important to give the 'why' with the 'how,' and not just the 'how.' I also believe in commenting my code

you are right, it helps people like us who are learners.
I appreciate it buddy. Once again thanks a lot