PL/SQL proc dies without a trace

From: EdStevens <quetico_man_at_yahoo.com>
Date: 19 Aug 2006 08:33:08 -0700
Message-ID: <1156001588.365870.170790@h48g2000cwc.googlegroups.com>

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 ...\n"
#--- 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?) Received on Sat Aug 19 2006 - 10:33:08 CDT