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 Go to next message
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 #398120 is a reply to message #398115] Wed, 15 April 2009 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQLplus has the bind variables set up with different datatypes to the PL/SQL would be my first and only guess. So trailing spaces or an implicit datatype conversion giving you grief.
Re: Cursor ignoring a record in query result [message #398126 is a reply to message #398120] Wed, 15 April 2009 05:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12415
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 Go to previous messageGo to next message
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 #398140 is a reply to message #398115] Wed, 15 April 2009 06:05 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
I reckon parceldate of the missing record isn't what you think it is.
Do
SELECT to_char(parceldate, 'DD-MON-YYYY HH24:MI:SS')

on the record in question.
Re: Cursor ignoring a record in query result [message #398174 is a reply to message #398140] Wed, 15 April 2009 08:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25045
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 #398197 is a reply to message #398180] Wed, 15 April 2009 10:50 Go to previous messageGo to next message
susanreid
Messages: 9
Registered: September 2008
Junior Member
I have attached an excel file that has the records that are on either side of the 'missing' record. Hopefully this will allow you to see the base info that I am working with. FYI All ID fields are defined as numbers, All DATE fields are defined as Date and the FIXEDLIFTING and SPLIT fields are also defined as numbers.

But surely if I have two identical SQL statements that refer to the unique ID of the record I am trying to find the SQL should bring back that record regardless of whether the select is run in a cursor in PL/SQL or in a SQL Plus window.

If you refer back to the code I posted, I removed the lines

l.SystemID = pSystemID
	  AND p.parceldate >= pStartDate
	  AND l.SPLIT = 1
          AND p.ProductID = pProductID


and replaced them with
liftingid = 202358


This refers to the single record that I am interested in. Surely Referring to the record directly removes the possibility of the variables being transformed somewhere in the code as I am not using them.

Changing the condition in the SQL returns the correct single record in SQL Plus, but no records at all in PL/SQL.

i hope there is enough information here now for some more help to be given.
  • Attachment: Book1.csv
    (Size: 0.94KB, Downloaded 108 times)
Re: Cursor ignoring a record in query result [message #398198 is a reply to message #398115] Wed, 15 April 2009 10:58 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Changing the condition in the SQL returns the correct single record in SQL Plus, but no records at all in PL/SQL.

So you claim.

Please realize that contrary to what you may imagine, SQL run from SQL*Plus can produce different results when embedded within PL/SQL.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Re: Cursor ignoring a record in query result [message #398415 is a reply to message #398115] Thu, 16 April 2009 03:36 Go to previous messageGo to next message
susanreid
Messages: 9
Registered: September 2008
Junior Member
i realise it must be doing something different but I don;t know what, which is why I'm here.

I have attached everything that is needed to recreate the table structure and the data

The expected results from the query are
LIFTINGID              CARGONOSUFFIX             LIFTINGDATE               UPPERLOADDATE             LOWERLOADDATE             WHOID                  WHOTYPE CARGOSIZE              SHIPPINGNO                ORIGINALLIFTINGCARGONO    CARGONO                                            CARGONOCOUNTER         PARCELCARGONOCOUNTER   VESSELID               JOINTLIFTINGID         FIXEDLIFTING           SPLIT                  LIFTINGSTATUSID        
---------------------- ------------------------- ------------------------- ------------------------- ------------------------- ---------------------- ------- ---------------------- ------------------------- ------------------------- -------------------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
202358                 A                         21-MAY-09                 22-MAY-09                 20-MAY-09                 100013                 C                              S-244A                    S-244                     S-244A\DO100A                                      244                    100                    100025                 0                      1                      1                      2                      
202443                 B                         24-MAY-09                 25-MAY-09                 23-MAY-09                 100013                 C                              S-244B                    S-244                     S-244B\DO100B                                      244                    100                    100025                 0                      1                      1                      2                      



The function that is causing the problem is earlier in the post. The results of the function are used to loop round later in the package and to create additional records based on the results.

The inputs to the function are

  PPRODUCTID	29	NUMBER
  PSTARTDATE	08-MAY-09 00.00.00	DATE
  PSYSTEMID	100028	NUMBER


The resulting collection from the function is
- TSPLITLIFTINGLIST	indexed table	TBLSPLITLIFTINGS
    - _values		TBLSPLITLIFTINGS element[1]
        - [1]		TBLSPLITLIFTINGS element
              _key	1	PLS_INTEGER
            - _value		RECSPLITLIFTING
                  CARGONO	'S-244B\DO100B'	VARCHAR2(50)
                  CARGONOCOUNTER	100	NUMBER
                  CARGONOSUFFIX	'B'	VARCHAR2(1)
                  CARGOSIZE	NULL	NUMBER
                  FIXEDLIFTING	1	NUMBER
                  JOINTLIFTINGID	0	NUMBER
                  KEEPPARCEL	NULL	BOOLEAN
                  LIFTDATE	24-MAY-09 00.00.00	DATE
                  LIFTINGID	202443	NUMBER
                  LIFTINGSTATUSID	2	NUMBER
                  LOWERLOADDATE	23-MAY-09 00.00.00	DATE
                  ORIGINALLIFTINGCARGONO	'S-244'	VARCHAR2(50)
                  SHIPPINGCOUNTER	244	NUMBER
                  SHIPPINGNO	'S-244B'	VARCHAR2(50)
                  SPLIT	1	NUMBER
                  UPPERLOADDATE	25-MAY-09 00.00.00	DATE
                  VESSELID	100025	NUMBER
                  WHOID	100013	NUMBER
                  WHOTYPE	'C'	VARCHAR2(1)


This is where I would expect to see two records in the collection

I hope this is now enough information.
  • Attachment: problem.sql
    (Size: 20.69KB, Downloaded 157 times)
Re: Cursor ignoring a record in query result [message #398425 is a reply to message #398115] Thu, 16 April 2009 04:10 Go to previous messageGo to next message
cookiemonster
Messages: 12415
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Confused

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;
Re: Cursor ignoring a record in query result [message #398439 is a reply to message #398115] Thu, 16 April 2009 04:56 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
That function, with the sample data provided gives a PL/SQL table with two rows.
The problem is somewhere else.
Previous Topic: Inserted Data not shown in the Table
Next Topic: Oracle10g
Goto Forum:
  


Current Time: Wed Dec 07 20:16:56 CST 2016

Total time taken to generate the page: 0.09218 seconds