Control not returning to UNIX when sqlplus is executed in while loop of shell script

Hi All,

I have observed some of the scripts gets stuck because sqlplus session is not completing it's session. This behavior is intermittent. I will give one of the scenario. Below is the code which executes one sql query.

exec_SQL()
{
   sql_Stmt=$1
   
   sql_Type=`echo $sql_Stmt | awk -F " " '{ printf toupper($1) }'`

   E_WLM_SQL_RESULT=`sqlplus -s /@${E_WLM_DB} <<!
      set pagesize 0 feedback off verify off heading off echo on
      whenever SQLERROR exit SQL.SQLCODE
      whenever OSERROR exit 9
      $sql_Stmt;
      commit;
   !`

   sql_RC=$?

   if [[ $sql_RC -eq 0 ]]; then
 
      str_Result=`echo $E_WLM_SQL_RESULT | grep SP2`  
  
      if [[ ${#str_Result} > 0 ]]; then
         sql_RC=57      # SQL Error
         sql_Code=0 
      else
         export E_WLM_SQL_RESULT
      fi
   else
      sql_Code=$sql_RC   # Store SQL.SQLCODE before overwriting
 
      if [[ $sql_RC -eq 9 ]]; then   # OSERROR
         sql_RC=91     
      else
         case $sql_Type in 
            INSERT) sql_RC=52 ;;
            UPDATE) sql_RC=53 ;;
            SELECT) sql_RC=54 ;;
            DELETE) sql_RC=56 ;;
            *) sql_RC=57 ;;
         esac
      fi
   fi
 
   if [[ $sql_RC -ne 0 ]]; then
      Log "Error in executing SQL Statement: $sql_Stmt "
      Log "SQL.SQLCODE=$sql_Code"
      Log "=====================================   ORACLE ERROR DESCRIPTION  ====================================="  
      Log "$E_WLM_SQL_RESULT"
      Log "======================================================================================================="
      Log "Returning with $sql_RC"

   fi
  
   return $sql_RC 
}

below is the unix session.
pwlm 8297 8290 0 00:16 ? 00:00:03 /bin/ksh -a /opt/XWLMLL02
pwlm 29121 8297 0 00:32 ? 00:00:00 sqlplus -s

The script has run a process internally and above code part is to monitor whether the process is completed. Even though the process is completed, the script has not moved further from this point. Upon checking further, I have seen one active sqlplus session. I suspect that the sqlplus session is from the above code and it is somehow stuck in turn the script has stuck.

I have observed this similar issue 3 times till now in the past 7 days. But, it is for different scripts. The logic remains the same.

This script was working fine in our old infrastructure where there was AIX server. We have not at all faced this issue over there.

Anyone, could help me to resolve the issue?

Thanks in Advance.

You need code tags, your post is hard to read

Start with only the sql, run it from the sqlplus prompt.
Next wrap JUST the sql that ran above in a shell wrapper. Execute it. Use the parameter associated with the problem.

One thing I've seen that causes hangs is the sqlplus gets what it thinks is garbage, usually caused by trying to quote or escape something so the shell will not wreck it, and passing it through the shell script to sqlplus. sqlpus hangs because it is waiting for more input, like to complete the quote for example.

As a first guess consider this is your problem, especially since it is intermittent. Intermittent == parameter problems

It a is a wait on stdin for sqlplus, I believe.

Thanks for the reply Jim.

So, you are suggesting this is a parameter problem?

Parameter remains the same for my script. That is the select query. I didn't get what you meant by garbage value.

The query executes within fraction of seconds. Initially, I thought that sleep for 1 second could be cause. But, I don't have anything to support it.

The issue is confirmed with the below

E_WLM_SQL_RESULT=`sqlplus -s /@${E_WLM_DB} <<!
      set pagesize 0 feedback off verify off heading off echo on
      whenever SQLERROR exit SQL.SQLCODE
      whenever OSERROR exit 9
      $sql_Stmt;
      commit;
   !`

I have added exit at the end of the sqlplus. Still, it didn't help. I am just wondering why we face the issue intermittently. This block of the code is executed by all the scripts. 50+ scripts. Internally, it calls the sqlplus block to execute. I think that sqlplus is executed for much much more than thousands and out of those once it gets stuck. I have faced this issue today as well. Still not able to fix the issue.

Put the end marker ! of the here doc at the very beginning of the line!
For example

   E_WLM_SQL_RESULT=`
sqlplus -s /@${E_WLM_DB} <<!
      set pagesize 0 feedback off verify off heading off echo on
      whenever SQLERROR exit SQL.SQLCODE
      whenever OSERROR exit 9
      $sql_Stmt;
      commit;
!
    `