Home » SQL & PL/SQL » SQL & PL/SQL » Cursor ignoring a record in query result (Oracle XE)
Cursor ignoring a record in query result [message #398115] |
Wed, 15 April 2009 05:05  |
susanreid
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
Hi
This is a bit weird and can't get to the bottom of it at all. I have a cursor declared where the results of a SQL query are stored in a collection.
When the code is stepped through the results never find one particular record. However if I run the SQL statement in a SQL window outwith the PLSQL code, the missing record is there.
Its as if the record doesn;t exist in the cursor.
Anyone have any ideas?
Regards
Susan
|
|
|
|
Re: Cursor ignoring a record in query result [message #398126 is a reply to message #398120] |
Wed, 15 April 2009 05:38   |
susanreid
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
I'm not using bind variables in the SQL Plus, just the actual values that are generated by the code ie the same values that are being used in the cursor.
The values that are being read into the PLSQL are exactly those that are being used in the query.
I have replaced the variables in the cursor with the actual values and it still can;t find the record.
Susan
|
|
|
Re: Cursor ignoring a record in query result [message #398131 is a reply to message #398115] |
Wed, 15 April 2009 05:46   |
cookiemonster
Messages: 13964 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Then presumably you've got bind variables in the PLSQL and constants in SQLplus - that's still a difference.
You need to look at the record that's going missing, there's probably something odd about it - either trailing spaces on one or more columns or a date that's not what you think it is.
If you want further help from us posting the code would make life easier.
|
|
|
Re: Cursor ignoring a record in query result [message #398137 is a reply to message #398131] |
Wed, 15 April 2009 05:58   |
susanreid
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
Here is the code for the Function that is causing me the problem.
When the records are created the variables that are used in the condition of the query are automatically generated by the PL/SQL elswhere, so I can't really understand why the columns would be storing anything odd for this particular record.
If I replace the SQL with exactly what i am using in SQL PLus ie not use the variables that are coming in to the function, it still does not find the record
FUNCTION RetrieveSplitLiftings(pSystemID IN NUMBER,
pProductID IN NUMBER,
pStartDate IN DATE) RETURN tblSplitLiftings
AS
--Declare Local Variables
tSplitLiftingList tblSplitLiftings;
nIndex NUMBER := 1;
nCount NUMBER ;
--Declare Cursors
CURSOR cSplitLifting IS
SELECT DISTINCT
l.LiftingID, SUBSTR(l.cargono,LENGTH(l.cargono),LENGTH(l.cargono)-1) AS CargoNoSuffix,
p.parceldate AS liftingDate,
l.upperloaddate, l.lowerloaddate,
DECODE(c.groupid, NULL, p.PartnerID, c.GROUPID) AS WhoID,
DECODE(c.GROUPID,NULL, 'P', 'C') AS WhoType,
l.CargoSize,
l.CargoNo AS ShippingNo,
SUBSTR(l.Cargono, 1, LENGTH(l.cargono)-1) AS OriginalLiftingCargoNo,
p.CargoNo AS CargoNo,
l.CargoNoCounter,
p.CARGONOCOUNTER AS ParcelCargoNoCounter,
l.VESSELID,
l.JointLiftingID,
l.FIXEDLIFTING, l.SPLIT, l.liftingstatusid
FROM ST_LIFTING l,
ST_PARCEL p,
ST_COLIFT c
WHERE l.SystemID = pSystemID
AND p.parceldate >= pStartDate
AND l.SPLIT = 1
AND p.ProductID = pProductID
AND l.LiftingID = p.LiftingID
AND p.partnerID = c.partnerID(+)
AND p.FieldID = c.fieldID(+)
ORDER BY Liftingdate;
BEGIN
FOR cvSplitLifting IN cSplitLifting LOOP
tSplitLiftingList(nIndex).LiftingID := cvSplitLifting.LiftingID;
tSplitLiftingList(nIndex).CargoNoSuffix := cvSplitLifting.CargoNoSuffix;
tSplitLiftingList(nIndex).LiftDate := cvSplitLifting.LiftingDate;
tSplitLiftingList(nIndex).UpperLoadDate := cvSplitLifting.UpperLoadDate;
tSplitLiftingList(nIndex).LowerLoadDate := cvSplitLifting.LowerLoadDate;
tSplitLiftingList(nIndex).WhoID := cvSplitLifting.WhoID;
tSplitLiftingList(nIndex).WhoType := cvSplitLifting.WhoType;
tSplitLiftingList(nIndex).CargoSize := cvSplitLifting.CargoSize;
tSplitLiftingList(nIndex).ShippingNo := cvSplitLifting.ShippingNo;
tSplitLiftingList(nIndex).OriginalLiftingCargoNo := cvSplitLifting.OriginalLiftingCargoNo;
tSplitLiftingList(nIndex).CargoNo := cvSplitLifting.CargoNo;
tSplitLiftingLIst(nIndex).ShippingCounter := cvSplitLifting.CargoNoCounter;
tSplitLiftingLIst(nIndex).CargoNoCounter := cvSplitLifting.ParcelCargoNoCounter;
tSplitLiftingList(nIndex).VesselID := cvSplitLifting.VesselID;
tSplitLiftingList(nIndex).JointLiftingID := cvSplitLifting.JointLiftingID;
tSplitLiftingList(nIndex).FixedLifting := cvSplitLifting.FixedLifting;
tSplitLiftingList(nIndex).SPLIT := cvSplitLifting.SPLIT;
tSplitLiftingList(nIndex).liftingstatusid := cvSplitLifting.liftingstatusid;
nIndex := nIndex + 1;
END LOOP;
RETURN tSplitLiftingList;
END RetrieveSplitLiftings;
|
|
|
|
Re: Cursor ignoring a record in query result [message #398174 is a reply to message #398140] |
Wed, 15 April 2009 08:53   |
susanreid
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
The parcel date is
TO_CHAR(PARCELDATE,'DD-MON-YYYYHH24:MI:SS')
-------------------------------------------
10-MAY-2009 00:00:00
10-MAY-2009 00:00:00
The pStartDate that is going in to the function is 08-MAY-2009 00:00:00
I have tried replacing all the conditions with a single condition liftingid = 202358, which is the unique ID number of the record that is being missed. It still can't find it.
|
|
|
Re: Cursor ignoring a record in query result [message #398180 is a reply to message #398115] |
Wed, 15 April 2009 09:52   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>It still can't find it.
In the future please be more vague to increase the challenge for providing a meaningful response.
When a discrepancy exists between what Oracle does & what user "expects", I am 100% certain that Oracle is behaving correctly.
Your challenge is to realize why Oracle does what it does.
Without your tables & data, there is not much we can do for you.
[Updated on: Wed, 15 April 2009 09:52] Report message to a moderator
|
|
|
|
|
|
Re: Cursor ignoring a record in query result [message #398425 is a reply to message #398115] |
Thu, 16 April 2009 04:10   |
cookiemonster
Messages: 13964 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I don't think the problem is your cursor. I think it's the PL/SQL table or the code that reads it.
SQL> CREATE OR REPLACE PROCEDURE test_cur (pSystemID IN NUMBER,
2 pProductID IN NUMBER,
3 pStartDate IN DATE) AS
4
5 CURSOR c_test IS
6 SELECT DISTINCT
7 l.LiftingID, SUBSTR(l.cargono,LENGTH(l.cargono),LENGTH(l.cargono)-1) AS CargoNoSuffix,
8 p.parceldate AS liftingDate,
9 l.upperloaddate, l.lowerloaddate,
10 DECODE(c.groupid, NULL, p.PartnerID, c.GROUPID) AS WhoID,
11 DECODE(c.GROUPID,NULL, 'P', 'C') AS WhoType,
12 l.CargoSize,
13 l.CargoNo AS ShippingNo,
14 SUBSTR(l.Cargono, 1, LENGTH(l.cargono)-1) AS OriginalLiftingCargoNo,
15 p.CargoNo AS CargoNo,
16 l.CargoNoCounter,
17 p.CARGONOCOUNTER AS ParcelCargoNoCounter,
18 l.VESSELID,
19 l.JointLiftingID,
20 l.FIXEDLIFTING, l.SPLIT, l.liftingstatusid
21 FROM ST_LIFTING l,
22 ST_PARCEL p,
23 ST_COLIFT c
24 WHERE l.SystemID = pSystemID
25 AND p.parceldate >= pStartDate
26 AND l.SPLIT = 1
27 AND p.ProductID = pProductID
28 AND l.LiftingID = p.LiftingID
29 AND p.partnerID = c.partnerID(+)
30 AND p.FieldID = c.fieldID(+)
31 ORDER BY Liftingdate;
32
33 BEGIN
34
35 FOR rec IN c_test LOOP
36
37 dbms_output.put_line('ID is '||to_char(rec.liftingid));
38 dbms_output.put_line('Date is '||to_char(rec.liftingdate, 'DD-MON-YYYY HH24:MI:SS'));
39
40 END LOOP;
41
42 END test_cur;
43 /
Procedure created.
SQL> set serveroutput on
SQL> exec test_cur(100028, 29, to_date('08-MAY-2009', 'DD-MON-YYYY'));
ID is 202358
Date is 21-MAY-2009 00:00:00
ID is 202443
Date is 24-MAY-2009 00:00:00
Two rows from an identical cursor using your test data.
I would suspect that either you're reading the PL/SQL table wrong or something is removing rows from it before you see it.
I didn't recreate your function because you didn't provide the code for tblSplitLiftings.
|
|
|
Re: Cursor ignoring a record in query result [message #398426 is a reply to message #398425] |
Thu, 16 April 2009 04:16   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Thanks for the comprehensive test case.
I get exactly the same results as @Cookiemonster - I get two rows back from the procedure matching the two rows back from the raw sql.
Are you passing the date parameter in as a date data type, or relying on an implicit type conversion?
|
|
|
Re: Cursor ignoring a record in query result [message #398434 is a reply to message #398115] |
Thu, 16 April 2009 04:44   |
susanreid
Messages: 9 Registered: September 2008
|
Junior Member |
|
|
Not entirely sure what you mean, all dates throughout are stored as dates and I am assuming that I pass them in as dates and they are used as dates. I don;t think I'm relying on any type of data conversion
Here is the definition for tblSplit Liftings
TYPE recSplitLifting IS RECORD(
LiftingID NUMBER,
CargoNoSuffix VARCHAR2(1),
WhoID NUMBER,
WhoType VARCHAR2(1),
LiftDate DATE,
UpperLoadDate DATE,
LowerLoadDate DATE,
ShippingNo VARCHAR2(50),
OriginalLiftingCargoNo VARCHAR2(50),
CargoNo VARCHAR2(50),
ShippingCounter NUMBER,
CargoNoCounter NUMBER,
VesselID NUMBER,
CargoSize NUMBER,
JointLiftingID NUMBER,
FixedLifting NUMBER,
SPLIT NUMBER,
KeepParcel BOOLEAN,
liftingstatusid NUMBER);
TYPE tblSplitLiftings IS TABLE OF recSplitLifting
INDEX BY BINARY_INTEGER;
|
|
|
|
Goto Forum:
Current Time: Thu Feb 13 11:05:50 CST 2025
|