You Asked

I am extracting data from complex XML documents into the database. The way that I have approached this is to write functions which handle lists of elements. So, the initial procedure opens the entire XMl document and I extract data from it using a cursor select from XMLTABLE. Data for child elements that repeat are returned as XMLTYPE fragments containing the list of those elements. I then call other functions passing these XMLTYPE fragments, depending on what element they are, where they are handled in a similar manner.

Now, I have a situation where I have this sort of XML:

...
<entryRelationship>
...
<observation>
...
<entryRelationship>
...
<observation>

So each function looks and works identically.

      
   FUNCTION insert_entryRelationships (p_msg_id          IN NUMBER,
                                       p_parent_entity   IN VARCHAR2,
                                       p_parent_eid      IN NUMBER,
                                       p_xml             IN XMLTYPE,
                                       p_text_xml        IN XMLTYPE)
      RETURN NUMBER
   IS
      TYPE extract_rt IS RECORD
      (
         EID                     NUMBER,
         MSG_ID                  NUMBER,
         PARENT_ENTITY           VARCHAR2 (1024),
         PARENT_EID              NUMBER,
         PARENT_SEQ              NUMBER,
         TYPE_CODE               VARCHAR2 (16),
         observationElements     XMLTYPE
      );

         CURSOR cs_extract (
         msg_id           NUMBER,
         parent_entity    VARCHAR,
         parent_eid       NUMBER,      
         erElements       XMLTYPE)
      IS
         SELECT NULL AS "EID",
                msg_id AS "MSG_ID",
                parent_entity AS "PARENT_ENTITY",
                parent_eid AS "PARENT_EID",
                er.entity_seq as "PARENT_SEQ",
                er.typeCode as "TYPE_CODE",
                er.observationElements as "observationElements" 
           FROM XMLTABLE (
                   xmlnamespaces (
                      DEFAULT 'urn:hl7-org:v3',
                      'urn:hl7-org:sdtc' AS "sdtc",
                      'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                   '/entryRelationship'
                   PASSING erElements
                   COLUMNS entity_seq            FOR ORDINALITY,
                           typeCode              VARCHAR2 (16) PATH '@typeCode',
                           observationElements   XMLTYPE PATH 'observation') er;

      TYPE tempTable IS TABLE OF extract_rt;

      TYPE tempEIDTable IS TABLE OF DW_GENESIS.E_ENTRY_RELATIONSHIP.EID%TYPE;

      v_table              tempTable;
      v_eid_table          tempEIDTable;
      v_records_inserted   NUMBER := 0;
      
   BEGIN
      IF (p_xml IS NULL)
      THEN
         DBMS_OUTPUT.put_line (
            'no entryRelationship element xml to process, returning 0');
      ELSE
         OPEN cs_extract (p_msg_id,
                          p_parent_entity,
                          p_parent_eid,
                          p_xml);

         LOOP
            FETCH cs_extract BULK COLLECT INTO v_table LIMIT 40;
            FORALL i IN 1 .. v_table.COUNT
               INSERT INTO DW_GENESIS.E_ENTRY_RELATIONSHIP
                    VALUES (v_table (i).EID,
                            v_table (i).MSG_ID,
                            v_table (i).PARENT_ENTITY,
                            v_table (i).PARENT_EID,
                            v_table (i).PARENT_SEQ,
                            v_table (i).TYPE_CODE)
                 RETURNING eid
                      BULK COLLECT INTO v_eid_table;

            FOR i IN 1 .. v_table.COUNT
            LOOP
             v_records_inserted :=
                 v_records_inserted
               + insert_observations (p_msg_id,
                                      'E_ENTRY_RELATIONSHIP',
                                      v_eid_table(i),
                                      v_table(i).observationElements,
                                      p_text_xml);

            END LOOP;

            EXIT WHEN cs_extract%NOTFOUND;
         END LOOP;
         CLOSE cs_extract;

      END IF;
      RETURN v_records_inserted;
   END;

It seems that when the function is invoked for entryRelationship, it then calls the function for the observation element, that recurses into the function for entryRelationship, but when it pops back up to the initial invocation of the entryRelationship function, I get the error:

[Error] ORA-20100: ORA-01002: fetch out of sequence

And I think this is because the cs_extract cursor was closed in the recursive call so that it fails when the LOOP ... END LOOP attempts to fetch the next chunk.

So, cursors are excluded from the recursive stack?

and Connor said...

No, cursors are private (and hence included on the recursive stack), eg

SQL> create table t as select rownum*10 x from dual
  2  connect by level <= 10 ;

Table created.

SQL>
SQL> create or replace
  2  procedure recurs(p_x int) is
  3    type nlist is table of number index by pls_integer;
  4    n nlist;
  5
  6    cursor C is select * from t where p_x <= 3;
  7  begin
  8    dbms_output.put_line('Opening at '||p_x);
  9    open c;
 10    dbms_output.put_line('Fetching at '||p_x||', starting row '||c%rowcount);
 11    fetch c bulk collect into n;
 12
 13    for i in 1 .. n.count loop
 14      dbms_output.put_line('Level '||p_x||': '||n(i));
 15    end loop;
 16
 17    if n.count > 0 then
 18       recurs(p_x+1);
 19    end if;
 20    dbms_output.put_line('Closing at '||p_x);
 21    close c;
 22  end;
 23  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec recurs(1)
Opening at 1
Fetching at 1, starting row 0
Level 1: 10
Level 1: 20
Level 1: 30
Level 1: 40
Level 1: 50
Level 1: 60
Level 1: 70
Level 1: 80
Level 1: 90
Level 1: 100
Opening at 2
Fetching at 2, starting row 0
Level 2: 10
Level 2: 20
Level 2: 30
Level 2: 40
Level 2: 50
Level 2: 60
Level 2: 70
Level 2: 80
Level 2: 90
Level 2: 100
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Closing at 3
Closing at 2
Closing at 1

PL/SQL procedure successfully completed.

Similarly, if we make it 'partial fetch' loops then we're still ok

SQL> create table t as select rownum*10 x from dual
  2  connect by level <= 10 ;

Table created.

SQL>
SQL> create or replace
  2  procedure recurs(p_x int) is
  3    type nlist is table of number index by pls_integer;
  4    n nlist;
  5
  6    cursor C is select * from t where p_x <= 3;
  7  begin
  8    dbms_output.put_line('Opening at '||p_x);
  9    open c;
 10    loop
 11      dbms_output.put_line('Fetching at '||p_x||', starting row '||c%rowcount);
 12      fetch c bulk collect into n limit 5;
 13
 14      for i in 1 .. n.count loop
 15        dbms_output.put_line('Level '||p_x||': '||n(i));
 16      end loop;
 17
 18      if n.count > 0 then
 19         recurs(p_x+1);
 20      end if;
 21      exit when c%notfound;
 22    end loop;
 23    dbms_output.put_line('Closing at '||p_x);
 24    close c;
 25  end;
 26  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec recurs(1)
Opening at 1
Fetching at 1, starting row 0
Level 1: 10
Level 1: 20
Level 1: 30
Level 1: 40
Level 1: 50
Opening at 2
Fetching at 2, starting row 0
Level 2: 10
Level 2: 20
Level 2: 30
Level 2: 40
Level 2: 50
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 5
Level 2: 60
Level 2: 70
Level 2: 80
Level 2: 90
Level 2: 100
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 10
Closing at 2
Fetching at 1, starting row 5
Level 1: 60
Level 1: 70
Level 1: 80
Level 1: 90
Level 1: 100
Opening at 2
Fetching at 2, starting row 0
Level 2: 10
Level 2: 20
Level 2: 30
Level 2: 40
Level 2: 50
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 5
Level 2: 60
Level 2: 70
Level 2: 80
Level 2: 90
Level 2: 100
Opening at 3
Fetching at 3, starting row 0
Level 3: 10
Level 3: 20
Level 3: 30
Level 3: 40
Level 3: 50
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 5
Level 3: 60
Level 3: 70
Level 3: 80
Level 3: 90
Level 3: 100
Opening at 4
Fetching at 4, starting row 0
Closing at 4
Fetching at 3, starting row 10
Closing at 3
Fetching at 2, starting row 10
Closing at 2
Fetching at 1, starting row 10
Closing at 1

PL/SQL procedure successfully completed.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Problem Resolved, I think

Andrew Hart, March 01, 2017 - 4:04 pm UTC

Thank you very much the quick response and the detailed answer to my specific question about whether or not CURSORS were included on the stack when functions are recursively called.

Armed with that knowledge, I started focusing on the EXIT WHEN conditions. It seems that changing my EXIT to this solved the problem:

EXIT WHEN cs_extract%NOTFOUND OR cs_extract%NOTFOUND IS NULL;

I'm sure the FETCH is executing, so I'm not certain how it could become NULL.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Problem Resolved, I think

More to Explore

PL/SQL demos

PL/SQL docs

Recursive function that uses FETCH cursor BULK COLLECT LIMIT

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Problem Resolved, I think

More to Explore

PL/SQL demos

PL/SQL docs