DevOps, CI/CD and Automation

HI All,

Tried to read an XML document which is column(transaction_document) for table(hr_api_transactions) using XMLDOM. This
column is a CLOB Column . I have tried to collect the values in Different
Columns from this Document. But now if the Document size exceeed 64K ; code is
unable to read the Document using Program(XMLDOM).

please find my code below...

PROCEDURE ag_xml_trig(p_document CLOB
,p_scorecard_id number)
AS
l_doc xmldom.domdocument;
l_nodelist xmldom.domnodelist;
l_nodelist1 xmldom.domnodelist;
l_nodelist2 xmldom.domnodelist;
l_nodelist3 xmldom.domnodelist;
l_nodelist4 xmldom.domnodelist;
l_nodelist5 xmldom.domnodelist;
l_node xmldom.domnode;
l_node2 xmldom.domnode;
l_node4 xmldom.domnode;
l_node_name VARCHAR2 (200);
l_node_value VARCHAR2 (200);
l_length NUMBER;
l_length1 NUMBER;
l_length2 NUMBER;
l_length3 NUMBER;
n DBMS_XMLDOM.domnode;
l_date_start VARCHAR2 (100);
l_transactionid VARCHAR2 (100);
BEGIN
l_doc := DBMS_XMLDOM.newdomdocument (P_DOCUMENT);
l_nodelist2 :=DBMS_XMLDOM.getelementsbytagname (l_doc, 'TransCtx'); --newly2
l_length2 := DBMS_XMLDOM.getlength (l_nodelist2);
l_node2 := DBMS_XMLDOM.item (l_nodelist2, 0);
l_nodelist3 := DBMS_XMLDOM.getchildnodes (l_node2);
l_length3 := DBMS_XMLDOM.getlength (l_nodelist3);
FOR j IN 0 .. l_length3 - 1 LOOP
l_node2 := DBMS_XMLDOM.item (l_nodelist3, j);
l_node_name := DBMS_XMLDOM.getnodename (l_node2);
l_node2 := DBMS_XMLDOM.getfirstchild (l_node2);
IF DBMS_XMLDOM.getnodetype (l_node2) = DBMS_XMLDOM.text_node THEN
IF (l_node_name = 'TransactionId') THEN
l_TransactionId := DBMS_XMLDOM.getnodevalue (l_node2);
END IF;
END IF;
END LOOP;
l_nodelist :=DBMS_XMLDOM.getelementsbytagname (l_doc, 'ObjectiveEORow');
l_length := DBMS_XMLDOM.getlength (l_nodelist);
FOR k IN 0 .. l_length - 1 LOOP
l_date_end := NULL;
l_date_start := NULL;
l_weightingpercent := NULL;
l_scorecardid := NULL;
l_node := DBMS_XMLDOM.item (l_nodelist, k);
l_nodelist1 := DBMS_XMLDOM.getchildnodes (l_node);
l_length1 := DBMS_XMLDOM.getlength (l_nodelist1);

FOR i IN 0 .. l_length1 - 1 LOOP
l_node := DBMS_XMLDOM.item (l_nodelist1, i);
l_node_name := DBMS_XMLDOM.getnodename (l_node);
l_node := DBMS_XMLDOM.getfirstchild (l_node);
IF DBMS_XMLDOM.getnodetype (l_node) = DBMS_XMLDOM.text_node THEN
IF (l_node_name = 'ObjectiveId') THEN
l_date_start := DBMS_XMLDOM.getnodevalue (l_node);
END IF;
END IF;
END LOOP;
INSERT INTO hrms_xml_trig_tbl(transaction_id
,objective_id
)
VALUES (l_transactionid
,l_date_start
);
END LOOP;
DBMS_XMLDOM.freedocument (l_doc);
END ag_xml_trig;

Is ther any other way to read CLOB documnet.

Please suggest me.

Thanks,
Visu