PL/Sql procedure: my cursor is ordered by date but the output that is written to a table is not ordered the same

Question

Only getting my head around cursor loops and the likes lately, so might be something very simple with my code that's causing the problem

I am using a cursor to spool through customer data to create an xml file. It needs to be sorted by date so that the most recent data is at the bottom of the xml file.

when I run the sql for the cursor, i can see the data is ordered by date. But when I run the entire procedure and check the output, it seems to be ordered by date but on closer inspection some of the records are not in the correct order.

here is the code I'm running. I've omitted a lot of the query as its just xml padding, but I don't think that should make a difference.

the output is written to a table, which i then copy and paste into notepad++. When checking the output table I can see that the order is wrong

drop table recs_xml_output;
create table recs_xml_output (XML_STRING VARCHAR2 (4000 char));

declare

  PROCEDURE p_generate_ohmpi_record 
  IS
 
    lv_string           VARCHAR2(10000 CHAR) := NULL;
    lv_date_format      VARCHAR2(20 CHAR)    := 'YYYY-MM-DD';
    lv_time_format      VARCHAR2(20 CHAR)    := 'HH24:MI:SS';
    n_id                PLS_INTEGER          := NULL;

    CURSOR c_patient_xml IS
    select *         
        from sbyn_transaction T
         where  timestamp >= '07-JAN-22 11.58.02.139977000'
        and timestamp <=  '07-JAN-22 17.51.26.054240000'
    ORDER BY TIMESTAMP; 
    
  begin
          
     for v_patient_xml in c_patient_xml

      loop
        
       lv_string := n_id||'<Person><SourceID>';
        lv_string := lv_string||v_patient_xml.lid||'</SourceID><PPSN>'||v_patient_xml.lid||'</PPSN>';
       
        lv_string := lv_string||'<PPSNLastUpdated>';
        lv_string := lv_string||TO_CHAR( v_patient_xml.pps_number_updated,lv_date_format )||'T'||TO_CHAR( v_patient_xml.pps_number_updated,lv_time_format)||'</PPSNLastUpdated>';
    
        lv_string := lv_string||'<Birth>';
        
         IF v_patient_xml.date_of_birth IS NOT NULL THEN
         lv_string := lv_string||'<DateOfBirth>'||TO_CHAR( v_patient_xml.date_of_birth,lv_date_format )||'T'||TO_CHAR( v_patient_xml.date_of_birth,lv_time_format)||'</DateOfBirth>';
        else lv_string := lv_string||'<DateOfBirth></DateOfBirth>';
        END IF;
        
       ...
        
        insert into recs_xml_output VALUES (lv_string);

      END LOOP;
   COMMIT;
  end p_generate_ohmpi_record; 

begin
    
   p_generate_ohmpi_record;
  

end;
/

Answer 1

The main issue with your code is that you aren't storing the ordering column in your output table, and you're relying on the rows being returned from that table in the order they were inserted.

Unfortunately, as it's a heap table, the order of insertion is not necessarily going to be the same as the order you retrieve them. In order to guarantee a specific ordering of the rows when selecting from a table, you need to have an order by clause.

Therefore you could do something like:

create table recs_xml_output (tstamp timestamp, XML_STRING VARCHAR2 (4000 char));

PROCEDURE p_generate_ohmpi_record 
IS
  ...

  CURSOR c_patient_xml IS
  select *         
  from   sbyn_transaction T
  where  timestamp >= '07-JAN-22 11.58.02.139977000'
  and    timestamp <=  '07-JAN-22 17.51.26.054240000'
  ORDER BY TIMESTAMP; 

begin
      
  for v_patient_xml in c_patient_xml
  loop
    ...

    insert into recs_xml_output (tstamp, xml_string)
    VALUES (v_patient_xml.timestamp, lv_string);

  END LOOP;

  COMMIT;

end p_generate_ohmpi_record;

select *
from   recs_xml_output
order by tstamp;

However, if your ultimate goal is simply to take your rows and output them as XML, you can do it in a single SQL statement:

WITH sbyn_transaction AS (SELECT 1 lid,
                                 to_timestamp('11/01/2022 11:25:57.136468', 'dd/mm/yyyy hh24:mi:ss.ff6') pps_number_updated,
                                 to_date('01/01/2000', 'dd/mm/yyyy') date_of_birth,
                                 'info 1' info_column
                          FROM dual
                          UNION ALL
                          SELECT 2 lid,
                                 to_timestamp('11/01/2022 11:23:46.115329', 'dd/mm/yyyy hh24:mi:ss.ff6') pps_number_updated,
                                 to_date('06/10/1979', 'dd/mm/yyyy') date_of_birth,
                                 'info 2' info_column
                          FROM dual
                          UNION ALL
                          SELECT 3 lid,
                                 to_timestamp('11/01/2022 11:24:08.951232', 'dd/mm/yyyy hh24:mi:ss.ff6') pps_number_updated,
                                 NULL date_of_birth,
                                 'info 3' info_column
                          FROM dual
                          UNION ALL
                          SELECT 4 lid,
                                 to_timestamp('11/01/2022 11:23:17.468329', 'dd/mm/yyyy hh24:mi:ss.ff6') pps_number_updated,
                                 to_date('29/03/1957', 'dd/mm/yyyy') date_of_birth,
                                 'info 4' info_column
                          FROM dual)
-- end of mimicking your table with data in it; main query below:
SELECT st.*,
       XMLELEMENT("Person",
                  XMLFOREST(lid AS "SourceID",
                            lid AS "PPSN",
                            to_char(pps_number_updated, 'yyyy-mm-dd"T"hh24:mi:ss') AS "PPSNLastUpdated"),
                  XMLELEMENT("Birth",
                             XMLFOREST(to_char(date_of_birth, 'yyyy-mm-dd"T"hh24:mi:ss') AS "DateOfBirth") AS "Birth"),
                  XMLFOREST(info_column AS "SomeData")).getclobval() xml_record
FROM   sbyn_transaction st
ORDER BY pps_number_updated;

       LID PPS_NUMBER_UPDATED                                DATE_OF_BIRTH INFO_COLUMN XML_RECORD
---------- ------------------------------------------------- ------------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         4 11-JAN-22 11.23.17.468329000                      29/03/1957    info 4      <Person><SourceID>4</SourceID><PPSN>4</PPSN><PPSNLastUpdated>2022-01-11T11:23:17</PPSNLastUpdated><Birth><DateOfBirth>1957-03-29T00:00:00</DateOfBirth></Birth><SomeData>info 4</SomeData></Person>
         2 11-JAN-22 11.23.46.115329000                      06/10/1979    info 2      <Person><SourceID>2</SourceID><PPSN>2</PPSN><PPSNLastUpdated>2022-01-11T11:23:46</PPSNLastUpdated><Birth><DateOfBirth>1979-10-06T00:00:00</DateOfBirth></Birth><SomeData>info 2</SomeData></Person>
         3 11-JAN-22 11.24.08.951232000                                    info 3      <Person><SourceID>3</SourceID><PPSN>3</PPSN><PPSNLastUpdated>2022-01-11T11:24:08</PPSNLastUpdated><Birth></Birth><SomeData>info 3</SomeData></Person>
         1 11-JAN-22 11.25.57.136468000                      01/01/2000    info 1      <Person><SourceID>1</SourceID><PPSN>1</PPSN><PPSNLastUpdated>2022-01-11T11:25:57</PPSNLastUpdated><Birth><DateOfBirth>2000-01-01T00:00:00</DateOfBirth></Birth><SomeData>info 1</SomeData></Person>