Oracle stored procedure vagaries 1

[sarahknz]

Hi

I'm working with 2 stored procedures written by someone else. My Oracle is "adequate" for the job I'm doing but this is stretching me a bit.

I have an application which calls these stored procedures and processes the data. One is happy with 35 rows returned, the other craps out at 7. Unfortunately we need that 7 to work!

So, here's the header for my SP...

CREATE OR REPLACE PACKAGE ctmsp_get_cust_alerts_pkg AS
	TYPE swId_table_type is table of NW_ALERTS.nwAlertsId%TYPE
             index by binary_integer;
	TYPE nwSeverity_table_type is table of NW_ALERTS.nwSeverity%TYPE
             index by binary_integer;
	TYPE swObjectType_table_type is table of NW_ALERTS.swObjectType%TYPE
             index by binary_integer;
	TYPE nwNote_table_type is table of NW_ALERTS.nwNote%TYPE
             index by binary_integer;

they're both identical!

We have out batch sizes etc, and they're both right. The code catching them is identical too - it's just IN parameeters and the inside the SP that differs. They use cursors and do some intensive processing but I'd have thought that wouldn't matter.

Any ideas?

http://sarah.pcpropertymanager.com

http://www.pcpropertymanager.com

http://www.propertyinvestor.info

 

[sem]

When 2 identical procedures produce different results then the only reason is different environment. According to my own experience most frequent mistakes are

1. Wrong expectations about resolving names (e.g. somebody assumes that the procedure selects from some table hidden behind a public synonym while his own schema contains a table with the same name and some test data);
2. implicit conversions with different NLS settings (4.5.2005 may be both April 5 and May 4);
3 (2a?) comparisons with wrong type conversion expectations. E.g. 'March 3'>'April 5'

But in any case you have not provided enough information to make better asumptions.

Regards, Dima

http://seminihin.narod.ru

 

[sarahknz]

Thanks for the reply. Turns out that I was focussed on the tricky looking out parameters and the seemingly benign parameter was the problem - very funny, not!

I sussed it just before leaving work so didn't have time to post on here and admit my embarrasment

Sarah

http://sarah.pcpropertymanager.com

http://www.pcpropertymanager.com

http://www.propertyinvestor.info