Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> PL/SQL - Inconsistent results - Help

PL/SQL - Inconsistent results - Help

From: Dennis Wetherell <dwether_at_mail.arco.com>
Date: 1998/01/27
Message-ID: <01bd2b46$502f8ac0$aed0e288@ato-10111>#1/1

PL/SQL 2.3.3
Oracle 7.3.3
AIX 4.1.5 Ever since upgrading to 7.3.3 from 7.1.4, I have been experiencing random, unexplained failures of packages and procedures. The latest, and most serious is described below. Although I have been able to find work around solutions to each of my problems, because of the random, irrational nature of these problems, I am losing confidence in Oracle and PL/SQL. Packages that worked flawlessly (I think) for the past 5 years are now failing to produce the expected results - no warning, no error, and no results.

Problem - I need to massage and move data nightly from our warehouse environment to a set of denormalized tables for adhoc access. When the load procs (packaged procs) are called from another package or from an anonymous PL/SQL proc, the load procs do not provide a complete result set.

begin

   schema.proc1;
   schema.proc2;
   schema.proc3;
   schema.proc4;
   schema.proc5;
   schema.proc6;

end;
/

The warehouse tables being read by proc6 contain 65 records to be transferred - when called in the above manner, proc6 returns only the first record of the 65. No errors - Procedure completed successfully according to Oracle.

However if called in the following manner from SQLPlus, proc6 returns the expected 65 records:

exec schema.proc1;
exec schema.proc2;
exec schema.proc3;
exec schema.proc4;
exec schema.proc5;
exec schema.proc6;

I've used the first method for years without problem, but it began giving bad results on upgrade to 7.3.3. Anyone have any ideas? Is there a patch, do I have to upgrade to still another version of Oracle?

Converting back to 7.1 is not an option. Calling Oracle and reporting a downed production system hasn't helped either. I've been on ignore for a week now. If I had any idea when or where problems would occur, I could rewrite my code, but these problems aren't predictable. I just have to wait for users report data problems.

Thanks

dwether_at_mail.arco.com
Dennis Wetherell Received on Tue Jan 27 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US