PL/SQL proc dies without a trace

Giganews Newsgroups
Subject: PL/SQL proc dies without a trace
Posted by:  EdStevens (quetico_m…@yahoo.com)
Date: 19 Aug 2006

Client environment: HP-UX  B.11.00 U 9000/800 622329393 unlimited-user
license
    Oracle 8.1.7.4

Server environment: SunOS 5.9 Generic_118558-25 sun4u sparc
SUNW,Sun-Fire-880
  Oracle8i Enterprise Edition Release 8.1.7.4.0
  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit

The 8i database contains nothing but a bunch of synonyms and a dblink
to the 10g database, so it serves as a proxy client for some programs
compiled with Pro*C 8.1.7.  That's a long story that I'll spare for
now, and yes I know it is an unsupported combination.  Tell that to the
suits .... ;-)

Problem:  Shell script on the HP client executes this:

#---- begin quote from shell scrip -----
echo "PRE-EOD in progress ..."
OraStr=`vdbctl -crmsg -G`
sql_filename=$OHHOME/sql/Db/preeod.sql
sqlplus -s $OraStr <<!
@$sql_filename
commit;
exit;
!
echo "PRE-EOD is done ...
"
#--- end quote

and the script preeod.sql looks like this:

-- ---begin quote from sql script ---
declare
  presentcutofftime char(20);

begin
  SELECT to_char(SYSDATE,'YYYYMMDDHH24MiSS')
    INTO presentcutofftime
    FROM DUAL;
--
dbms_output.put_line('select of sysdate completed');
--
INSERT ...
--
COMMIT;
--
UPDATE ...
--
COMMIT;
--
UPDATE ...
--
COMMIT;
--
INSERT ...
--
COMMIT;
--
END;
/
exit;
-- --- end quote ---

The shell script is executed once a day as part of end-of-day
processing, and has been running on this platform since the first of
June.  On Wednesday the sql script failed to run and did not return any
error msgs.  After the first failure, we placed 'dbms_output' lines
immediately after each SQL statement, to get a better handle on things.
It was successful on the next (Thursday) run. Firday failed and the
only dbms_output line we got was the first one - after the SELECT ..
FROM DUAL.  So we are getting a connection and submitting the sql, but
it appears to fail in executing the first INSERT, and dies without a
trace.

We are working a TAR with Oracle Support and they are being as helpful
as they can, given the unsupported nature of our architecture.  I
thought it might be helpful to get a 10046 trace to get a finer-grained
picture.  I really only have two options (that I see) for initiating
that trace.  First is to add the necessary ALTER SESSION statements to
the sql script itself.  We actually did that, but the session did not
fail; in addition, setting the trace there means we're tracing the
session in the 8i database, which is not where the work is really being
done .. we're essentially tracing a client.  The second option is to
set an AFTER LOGON trigger on the 10g database.  I've done this
several times in other situations, using this code:

create or replace trigger logon_trigger
after logon on database
begin
  if (user = '***some username***' ) then
      execute immediate 'ALTER SESSION SET
TRACEFILE_IDENTIFIER=''UC7001''';
      execute immediate 'alter session set timed_statistics=true';
      execute immediate 'ALTER SESSION SET EVENTS ''10046 TRACE NAME
CONTEXT FOREVER, LEVEL 12''';
  end if;
  exception
    when others then
    null;
    end;
end;

But this one presents a special challenge.  Since the only real client
the 10g database has is the 8i exercising db_links it would seem that
it would seem that there would be no information available to the
trigger to know whether or not actually set the trace for any given
connection.  Am I overlooking something in this respect?

Since this part of the app doesn't require the 8i intermediary, we're
trying to get the apps people to connect directly to the 10g for this
process, but that has also been a struggle partly due to change control
restrictions.

Any suggestions on how I might proceed (other than application of a
lead pipe to the people who mandated this architecture?)

Replies