Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> PL/SQL - Inconsistent results - Help
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;
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