Re: PL/SQL proc dies without a trace

EdStevens wrote:
> 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?)

Looks like a procedure written with no attention to Oracle. What is the point of the incremental commits other than fishing for an ORA-01555?

And why a shell script? Write it as a database job and use DBMS_APPLICATION_INFO to trace the progress.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org