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?
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.