Shell Programming and Scripting

We have a function defined in a ksh script that gets called repeatedly and concurrently by background jobs called from within the same shell script file to run PL/SQL commands against an Oracle database. The function is below:

Code:

 
runPLSQL()
{
   ORACLE_SID=${1}
   procName=${2}
   
   # Set up proper Oracle environment #
   unset ORAENV_ASK
   ORAENV_ASK=NO
   . oraenv
   
   # Now execute the PL/SQL code #
   VALUE=`sqlplus -s ${ORACLE_USERNAME_PASSWORD} <<_EOF_
   whenever oserror exit failure
   whenever sqlerror exit sql.sqlcode
   set heading off
   set recsep off
   set feedback off
   set pagesize 0
   set linesize 100
   set trimspool on
   set serverout on size 1000000
   exec ${procName}
   quit
_EOF_`
 
}

This works 99% of the time, but every once in a while it does not execute the steps within the block:

whenever oserror exit failure
whenever sqlerror exit sql.sqlcode
set heading off
set recsep off
set feedback off
set pagesize 0
set linesize 100
set trimspool on
set serverout on size 1000000
exec ${procName}
quit

Using set -x in the script reveals the following output on the 1% occasion:

+ sqlplus -s /
+ 0<< \_EOF_
_EOF_

So, it shows that none of the hard coded commands between the first line and the last line of the block are sent to sqlplus.

In the 99% case, the following is the output from set -x

+ sqlplus -s /
+ 0<< \_EOF_
whenever oserror exit failure
whenever sqlerror exit sql.sqlcode
set heading off
set recsep off
set feedback off
set pagesize 0
set linesize 100
set trimspool on
set serverout on size 1000000
<plsql call was here>
quit
_EOF_


Has anyone ever seen this behavior before? We are running Red Hat Enterprise Linux Server release 5.3 (Tikanga)

It won't print the here-document until the program actually reads it. Try this:

Code:

# echo ignores stdin
echo <<EOF
a
b
c
d
EOF
# cat does not
cat <<EOF
q
w
e
r
t
y
EOF

So it's not the shell that's ignoring the here-document, just sqlplus...

Check $? after you run sqlplus there to see what its return code is, that might help find a clue why.

Maybe /tmp was full at the time?

I added the check on $? after the sqlplus and it returned zero.

+ sqlplus -s /
+ 0<< \_EOF_
_EOF_
+ VALUE=''
+ sqlplusRtnCd=0

I changed the sqlplus whenever to be as follows as well but it still returned zero.

whenever oserror exit 100
whenever sqlerror exit 200

I will check on the /tmp space issue when it runs tomorrow. Thanks.

Exactly how does the recursion process work and at what frequency. What username (other than "root") is executing the recursive process.

Why ask this?
It is quite easy to exceed your configured kernel limits with a recursive process.

It is a non-root user and it is not really recursion as it is not calling itself. The runPLSQL function is being called by another function (function1 for example) in a loop lower in the same script file in the background.

That loop keeps up to 50 calls to function1 running concurrently using background tasks and function1 calls runPLSQL 3 times for each execution.

Time to look at the maximum number of processes allowed by a non-root user in your kernel. You appear to be describing these symptoms but have evaded the question.
Maybe try:

Code:

ps -fuwhateveruser | wc -l

and compare with the kernel configuration for the maximum number of processes allowed per user.