Migrating from Oracle to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL may be challenging if the application is using the DBMS_XMLDOM package to access XML type objects. This is because the DBMS_XMLDOM package isn’t supported in Aurora PostgreSQL or Amazon RDS for PostgreSQL. Moreover, converting procedures to Aurora PostgreSQL or Amazon RDS for PostgreSQL could be a cumbersome and time-consuming process depending on the amount of stored procedures dependent on the DBMS_XMLDOM package.
In this post, we discuss a solution that uses the JSON data type instead of XML to migrate stored procedures from Oracle to PostgreSQL and reduce migration efforts. We chose JSON because JSON is the native format for data in JavaScript, and PostgreSQL has extensive support of JSON compared to XML.
Overview of solution
The DBMS_XMLDOM package in Oracle is used to access XML type objects and implements the Document Object Model (DOM), an application programming interface for HTML and XML documents.
In this solution, we create the DBMS_XMLDOM schema and wrapper package functions equivalent to Oracle in a PostgreSQL database using the PLV8 extension. PLV8 is a trusted JavaScript language extension for PostgreSQL that you can use for stored procedures, triggers, and more.
This solution uses the JSON data type instead of XML to convert Oracle’s DBMS_XMLDOM package to PostgreSQL. Therefore, make sure to complete a thorough assessment of the database and its dependent applications before proceeding with this solution and make sure there are no objections or concerns to using the JSON data type.
Prerequisites
Assuming that you have an Oracle and a PostgreSQL databases up and running, proceed with the following steps.
Before getting started, you have to create several database objects in PostgreSQL:
Create the DBMS_XMLDOM schema in PostgreSQL:
CREATE SCHEMA dbms_xmldom AUTHORIZATION postgres;
Install the plv8 extension:
CREATE EXTENSION plv8;
PLV8 is a shared library that provides a PostgreSQL procedural language powered by the V8 JavaScript engine.
Create the following wrapper functions under the DBMS_XMLDOM schema:
Wrapper function dbms_xmldom.addelement (this function adds new element into the document):
CREATE OR REPLACE FUNCTION dbms_xmldom.addelement(
tempjson json,
mykey text,
myval text)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
let tempObj = {};
tempObj[keyName] = val;
myjson[Object.keys(myjson)[0]] = tempObj;
}
else if(typeof(tj) === ‘object’){
myjson[Object.keys(myjson)[0]][keyName] = val;
}
else if(typeof(tj) === ‘string’){
let tempObj1 = {};
tempObj1[‘#text’] = tj;
tempObj1[keyName] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}
return myjson;
$BODY$;
Wrapper function dbms_xmldom.getattributes (this function retrieves the attributes of the node):
CREATE OR REPLACE FUNCTION dbms_xmldom.getattributes(
nodelist json,
b text)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let response = {};
if (nodelist == null) {
return null
}
else
{
let attrJson=Object.values(nodelist)[0];
Object.entries(attrJson).forEach(([keyname,val])=>{
if(keyname.indexOf(b) !== -1)
{ keyname = keyname.substring(1);
response[keyname]=val;}
});
}
return response;
$BODY$;
Wrapper function dbms_xmldom.getchildrenbytagname (returns the children of the document):
CREATE OR REPLACE FUNCTION dbms_xmldom.getchildrenbytagname(
nodelist json,
x text)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let res = {};
let nodename = x;
for (const [keyname, val] of Object.entries(nodelist)) {
if(keyname === nodename){
res[keyname] = val;
return res;
}
}
$BODY$;
Wrapper function dbms_xmldom.getelementbytagname (returns the element in the subtree by the tag name):
CREATE OR REPLACE FUNCTION dbms_xmldom.getelementbytagname(
nodelist json,
b text)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let response = {};
if (nodelist == null) {
return null
}
else {
let attrJson=Object.values(nodelist)[0];
Object.entries(attrJson).forEach(([keyname,val])=>{
if(keyname.indexOf(b) !== -1)
{response[keyname]=val;}
});
}
return response;
$BODY$;
Wrapper function dbms_xmldom.getlength (retrieves the number of items in the map):
CREATE OR REPLACE FUNCTION dbms_xmldom.getlength(
nodelist json)
RETURNS integer
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
if (nodelist == null)
return 0
else
return Object.keys(nodelist).length
$BODY$;
Wrapper function dbms_xmldom.getnodename (retrieves the name of the node):
CREATE OR REPLACE FUNCTION dbms_xmldom.getnodename(
nodelist json)
RETURNS text
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
return Object.keys(nodelist)[0];
$BODY$;
Wrapper function dbms_xmldom.getnodevalue (retrieves the value of the node):
CREATE OR REPLACE FUNCTION dbms_xmldom.getnodevalue(
nodelist json)
RETURNS text
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
return Object.values(nodelist)[0];
$BODY$;
Wrapper function dbms_xmldom.item (retrieves the item given the index in the map):
CREATE OR REPLACE FUNCTION dbms_xmldom.item(
nodelist json,
index integer)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let Obj = {};
let keyName = Object.keys(nodelist)[index-1];
let val = Object.values(nodelist)[index-1];
Obj[keyName] = val;
return Obj;
$BODY$;
Wrapper function dbms_xmldom.setattribute (sets the attribute specified by name; the function is overloaded with TEXT, INTEGER, and DOUBLE PRECISION data types):
CREATE OR REPLACE FUNCTION dbms_xmldom.setattribute(
tempjson json,
mykey text,
myval text)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
let tempObj = {};
tempObj[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj;
}
else if(typeof(tj) === ‘object’){
myjson[Object.keys(myjson)[0]][`@${keyName}`] = val;
}
else if(typeof(tj) === ‘string’){
let tempObj1 = {};
tempObj1[‘#text’] = tj;
tempObj1[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}
return myjson;
$BODY$;
CREATE OR REPLACE FUNCTION dbms_xmldom.setattribute(
tempjson json,
mykey text,
myval integer)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
let tempObj = {};
tempObj[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj;
}
else if(typeof(tj) === ‘object’){
myjson[Object.keys(myjson)[0]][`@${keyName}`] = val;
}
else if(typeof(tj) === ‘string’){
let tempObj1 = {};
tempObj1[‘#text’] = tj;
tempObj1[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}
return myjson;
$BODY$;
CREATE OR REPLACE FUNCTION dbms_xmldom.setattribute(
tempjson json,
mykey text,
myval double precision)
RETURNS json
LANGUAGE ‘plv8’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
let myjson = tempjson;
let tj = Object.values(myjson)[0];
let keyName = mykey;
let val = myval;
if(tj.length === 0){
let tempObj = {};
tempObj[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj;
}
else if(typeof(tj) === ‘object’){
myjson[Object.keys(myjson)[0]][`@${keyName}`] = val;
}
else if(typeof(tj) === ‘string’){
let tempObj1 = {};
tempObj1[‘#text’] = tj;
tempObj1[`@${keyName}`] = val;
myjson[Object.keys(myjson)[0]] = tempObj1;
}
return myjson;
$BODY$;
Example 1: Compare number of nodes
Let’s use the DBMS_XMLDOM.GETLENGTH function to return the number of nodes in the list and compare the output between Oracle and PostgreSQL.
Run the following code block in Oracle:
DECLARE
xmlDocument dbms_xmldom.DOMDocument;
xmlParams xmltype := xmltype(‘<Deptartments>
<Dept DeptID=”10″>
<Name>Administration</Name>
<Location>Seattle</Location>
</Dept>
<Dept DeptID=”20″>
<Name>Marketing</Name>
<Location>Toronto</Location>
</Dept>
</Deptartments>’);
domNode dbms_xmldom.DOMNode;
domElement dbms_xmldom.DOMElement;
nodeList dbms_xmldom.DOMNodeList;
v_getlenght int;
BEGIN
xmlDocument := dbms_xmldom.newDOMDocument(xmlParams);
domElement := dbms_xmldom.getdocumentelement(xmlDocument);
domNode := dbms_xmldom.makeNode(domElement);
nodeList := dbms_xmldom.getchildnodes(domNode);
select dbms_xmldom.getlength(nodeList) INTO v_getlenght from dual;
dbms_output.put_line(‘v_getlenght = ‘||to_char(v_getlenght));
END;
You get the following output.
In order to call the DBMS_XMLDOM.GETLENGTH function in PostgreSQL, we have to first convert the XML to JSON format.
XML
JSON
<Deptartments>
<Dept DeptID=”10″>
<Name>Administration</Name>
<Location>Seattle</Location>
</Dept>
<Dept DeptID=”20″>
<Name>Marketing</Name>
<Location>Toronto</Location>
</Dept>
</Deptartments>
[{
“@DeptID”: “10”,
“Name”: “Administration”,
“Location”: “Seattle”
},
{
“@DeptID”: “20”,
“Name”: “Marketing”,
“Location”: “Toronto”
}]
Run the following code in PostgreSQL:
DO $$
<<first_block>>
DECLARE
xmlParams json := ‘[
{
“@DeptID”: “10”,
“Name”: “Administration”,
“Location”: “Seattle”
},
{
“@DeptID”: “20”,
“Name”: “Marketing”,
“Location”: “Toronto”
}
]’;
v_getlenght int;
BEGIN
select dbms_xmldom.getlength(xmlParams) into v_getlenght;
raise notice ‘v_getlenght=%’, v_getlenght;
END first_block $$;
You get the following output.
Example 2: Generate an XML document
Let’s create a stored procedure to generate an XML document using the DBMS_XMLDOM package function.
This procedure covers the following DBMS_XMLDOM package functions:
dbms_xmldom.getdocumentelement
dbms_xmldom.makeNode
dbms_xmldom.makeNode
dbms_xmldom.createElement
dbms_xmldom.appendChild
dbms_xmldom.setattribute
The following Oracle procedure code generates the XML document:
CREATE OR REPLACE NONEDITIONABLE FUNCTION FN_DateToPlSqlXml (theDate DATE) RETURN VARCHAR2 IS
BEGIN
RETURN(to_char(theDate, ‘yyyy-mm-dd’) || ‘T’ || to_char(theDate, ‘hh24:mi:ss’));
END;
CREATE OR REPLACE PROCEDURE Generate_XML
(
tranID NUMBER,
transDate DATE,
transAmt NUMBER,
blobText1 VARCHAR2,
blobText2 VARCHAR2,
xmlParams CLOB,
xmlDocument IN OUT NOCOPY dbms_xmldom.DOMDocument
) IS
cdataNode dbms_xmldom.DOMCdataSection;
xmlNode dbms_xmldom.DOMNode;
nodeList dbms_xmldom.DOMNodeList;
mydocNode dbms_xmldom.DOMNode;
mydocElement dbms_xmldom.DOMElement;
queueNode dbms_xmldom.DOMNode;
queueElement dbms_xmldom.DOMElement;
blobNode dbms_xmldom.DOMNode;
blobElement dbms_xmldom.DOMElement;
BEGIN
/* Create main document with root tag */
IF NOT dbms_xmldom.isnull(xmlDocument) THEN
mydocElement := dbms_xmldom.getdocumentelement(xmlDocument);
mydocNode := dbms_xmldom.makeNode(mydocElement);
ELSIF xmlParams IS NOT NULL THEN
xmlDocument := dbms_xmldom.newDOMDocument(xmlParams);
mydocElement := dbms_xmldom.getdocumentelement(xmlDocument);
mydocNode := dbms_xmldom.makeNode(mydocElement);
ELSE
xmlDocument := dbms_xmldom.newDOMDocument;
xmlNode := dbms_xmldom.makeNode(xmlDocument);
mydocElement := dbms_xmldom.createElement(xmlDocument, ‘MYDOC’);
mydocNode := dbms_xmldom.appendChild(xmlNode, dbms_xmldom.makeNode(mydocElement));
END IF;
/*————————–*/
/* Create XML structure */
/*————————–*/
nodeList := dbms_xmldom.getchildrenbytagname(mydocElement, ‘PROCESS_QUEUE’);
IF dbms_xmldom.getlength(nodeList) = 0 THEN
queueElement := dbms_xmldom.createElement(xmlDocument, ‘PROCESS_QUEUE’);
queueNode := dbms_xmldom.appendChild(mydocNode, dbms_xmldom.makeNode(queueElement));
ELSE
queueNode := dbms_xmldom.item(nodeList, 0);
queueElement := dbms_xmldom.makeelement(queueNode);
END IF;
IF tranID IS NOT NULL THEN
dbms_xmldom.setattribute(queueElement, ‘tranID’, tranID);
END IF;
IF transDate IS NOT NULL THEN
dbms_xmldom.setattribute(queueElement, ‘transDate’, FN_DateToPlSqlXml(transDate));
END IF;
IF transAmt IS NOT NULL THEN
dbms_xmldom.setattribute(queueElement, ‘transAmt’, transAmt);
END IF;
IF blobText1 IS NOT NULL THEN
blobElement := dbms_xmldom.createElement(xmlDocument, ‘BLOB_TEXT1’);
blobNode := dbms_xmldom.appendChild(queueNode, dbms_xmldom.makeNode(blobElement));
cdataNode := dbms_xmldom.createcdatasection(xmlDocument, blobText1);
xmlNode := dbms_xmldom.appendChild(blobNode, dbms_xmldom.makeNode(cdataNode));
END IF;
IF blobText2 IS NOT NULL THEN
blobElement := dbms_xmldom.createElement(xmlDocument, ‘BLOB_TEXT2’);
blobNode := dbms_xmldom.appendChild(queueNode, dbms_xmldom.makeNode(blobElement));
cdataNode := dbms_xmldom.createcdatasection(xmlDocument, blobText2);
xmlNode := dbms_xmldom.appendChild(blobNode, dbms_xmldom.makeNode(cdataNode));
END IF;
Return;
END;
The following is the equivalent PostgreSQL procedure code:
CREATE OR REPLACE FUNCTION fn_datetoplsqlxml(
thedate timestamp )
RETURNS text
LANGUAGE ‘plpgsql’
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
RETURN concat(TO_CHAR(current_date, ‘yyyy-mm-dd’),’T’, to_char(current_timestamp, ‘HH24:MI:SS’));
END;
$BODY$;
CREATE OR REPLACE PROCEDURE Generate_XML(
tranID integer,
transDate timestamp,
transAmt double precision,
blobtext1 varchar(200),
blobtext2 varchar(200),
xmlparams text,
INOUT xmldocument json)
LANGUAGE ‘plpgsql’
AS $BODY$
DECLARE jsndocument JSON;
BEGIN
IF dbms_xmldom.getlength(xmldocument) > 0 THEN
jsndocument := xmldocument ;
ELSEIF xmlParams IS NOT NULL THEN
jsndocument := json(xmlParams);
ELSE
jsndocument := ‘{}’;
end IF;
jsndocument := dbms_xmldom.getchildrenbytagname(jsndocument,’PROCESS_QUEUE’);
If dbms_xmldom.getlength(jsndocument) = 0 THEN
jsndocument := ‘{“PROCESS_QUEUE” : “”}’;
end IF;
IF tranID IS NOT NULL THEN
jsndocument := dbms_xmldom.setattribute(jsndocument, ‘tranID’, tranID);
END IF;
IF transDate IS NOT NULL THEN
jsndocument = dbms_xmldom.setattribute(jsndocument, ‘transDate’, fn_datetoplsqlxml(transDate));
END IF;
IF transAmt IS NOT NULL THEN
jsndocument := dbms_xmldom.setattribute(jsndocument, ‘transAmt’, transAmt);
END IF;
IF blobText1 IS NOT NULL THEN
jsndocument := dbms_xmldom.addelement(jsndocument,’BLOB_TEXT1′,blobText1);
END if;
IF blobText2 IS NOT NULL THEN
jsndocument := dbms_xmldom.addelement(jsndocument,’BLOB_TEXT2′,blobText2);
END IF;
xmldocument := jsndocument;
RETURN;
END;
$BODY$;
Test case 1
In the following Oracle call statement, xmlParams is NULL:
set serveroutput on;
declare
tranID NUMBER := 11011;
transDate DATE := sysdate;
transAmt NUMBER := 999.99;
blobText1 VARCHAR(200) := ‘Credit Card <Refer charges below>’;
blobText2 VARCHAR(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
xmlParams CLOB := NULL;
xmlDocument dbms_xmldom.DOMDocument;
vClob CLOB;
begin
Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
–print the xml
dbms_lob.createtemporary(vClob, false);
dbms_xmldom.writetoclob( doc => xmlDocument, cl => vClob);
dbms_output.put_line(vClob);
end ;
The following screenshot shows our output.
The following is the equivalent PostgreSQL call statement:
DO $$
<<first_block>>
declare tranID integer := 11011;
declare transDate timestamp := current_date;
declare transAmt double precision := 999.99;
declare blobText1 varchar(200) := ‘Credit Card <Refer charges below>’;
declare blobText2 varchar(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
declare xmlParams text := NULL;
declare xmlDocument json;
declare jsndocument json;
BEGIN
call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2, xmlParams, jsndocument);
raise notice ‘%’,jsndocument;
END first_block $$;
You get the following output.
Test case 2
In the following Oracle call statement, xmlParams contains a value:
set serveroutput on;
declare
tranID NUMBER := 11022;
transDate DATE := ’04-Jul-2022′;
transAmt NUMBER := 888.88;
blobText1 VARCHAR(200) := ‘Credit Card <Refer charges below>’;
blobText2 VARCHAR(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
xmlParams CLOB := ‘<ROOT companyID = “9999” >
<PROCESS_QUEUE totalAmt = “1000”>
</PROCESS_QUEUE>
</ROOT>’;
xmlDocument dbms_xmldom.DOMDocument;
vClob CLOB;
begin
Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
–print the xml
dbms_lob.createtemporary(vClob, false);
dbms_xmldom.writetoclob( doc => xmlDocument, cl => vClob);
dbms_output.put_line(vClob);
end ;
The following screenshot shows our output.
The following code is the equivalent PostgreSQL call statement:
DO $$
<<first_block>>
declare tranID integer := 11022;
declare transDate timestamp := ’04-Jul-2022′;
declare transAmt double precision := 888.88;
declare blobText1 varchar(200) := ‘Credit Card <Refer charges below>’;
declare blobText2 varchar(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
declare xmlParams text := ‘{“@companyID”: “9999”,
“PROCESS_QUEUE”: {“@totalAmt”: “1000”}
}’;
declare xmlDocument json;
declare jsndocument json;
BEGIN
call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2,
xmlParams, jsndocument);
raise notice ‘%’,jsndocument;
END first_block $$;
We get the following output.
Example 3: Process the XML document
Let’s create another stored procedure to process the XML document. This procedure accepts the XML document created by the Generate_XML procedure as the input parameter, reads all the items, and stores them in the TRANSACTION_DETAILS table.
This procedure covers the following DBMS_XMLDOM package functions:
dbms_xmldom.getattributes
dbms_xmldom.getnodevalue
dbms_xmldom.item
dbms_xmldom.getelementbytagname
dbms_xmldom.getlength
Let’s create the TRANSACTION_DETAILS table in our databases.
Oracle
PostgreSQL
CREATE TABLE TRANSACTION_DETAILS
(
tranID NUMBER NOT NULL PRIMARY KEY,
transDate DATE NOT NULL,
transAmt NUMBER,
totalAmt NUMBER,
blobText1 CHAR,
blobText2 CHAR,
modified_time timestamp default sysdate
);
CREATE TABLE TRANSACTION_DETAILS
(
tranID INTEGER NOT NULL PRIMARY KEY,
transDate DATE NOT NULL,
transAmt DOUBLE PRECISION,
totalAmt DOUBLE PRECISION,
blobText1 TEXT,
blobText2 TEXT,
modified_time TIMESTAMP default CURRENT_TIMESTAMP
);
The following Oracle code creates the Process_XML procedure:
CREATE OR REPLACE PROCEDURE Process_XML
(
xmlDocument IN dbms_xmldom.DOMDocument,
errCode OUT NUMBER,
errMessage OUT VARCHAR2
) AS
xactdocElement dbms_xmldom.DOMElement;
xactdocNode dbms_xmldom.DOMNode;
queueNode dbms_xmldom.DOMNode;
blob1Node dbms_xmldom.DOMNode;
blob2Node dbms_xmldom.DOMNode;
attributeList dbms_xmldom.DOMNamedNodeMap;
attributeIndex NUMBER;
attributeNode dbms_xmldom.DOMNode;
nodeList dbms_xmldom.DOMNodeList;
nodeIndex NUMBER;
nodeNode dbms_xmldom.DOMNode;
v_tranID NUMBER;
v_transDate DATE;
v_transAmt NUMBER;
v_totalAmt NUMBER;
v_blobText1 VARCHAR2(200);
v_blobText2 VARCHAR2(200);
BEGIN
xactdocElement := dbms_xmldom.getdocumentelement(xmlDocument);
xactdocNode := dbms_xmldom.makeNode(xactdocElement);
nodeList := dbms_xmldom.getchildnodes(xactdocNode);
FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP
nodeNode := dbms_xmldom.item(nodeList, nodeIndex -1);
attributeList := dbms_xmldom.getattributes(nodeNode);
CASE dbms_xmldom.getnodename(nodeNode)
WHEN ‘PROCESS_QUEUE’ THEN
queueNode := nodeNode;
FOR attributeIndex IN 1..dbms_xmldom.getlength(attributeList) LOOP
attributeNode := dbms_xmldom.item(attributeList, attributeIndex-1);
CASE dbms_xmldom.getnodename(attributeNode)
WHEN ‘tranID’ THEN
v_tranID := dbms_xmldom.getnodevalue(attributeNode);
WHEN ‘totalAmt’ THEN
v_totalAmt := dbms_xmldom.getnodevalue(attributeNode);
WHEN ‘transDate’ THEN
v_transDate := XN_DateFromPlSqlXml(dbms_xmldom.getnodevalue(attributeNode));
WHEN ‘transAmt’ THEN
v_transAmt := dbms_xmldom.getnodevalue(attributeNode);
ELSE
NULL;
END CASE;
END LOOP;
ELSE
NULL;
END CASE;
END LOOP;
/*———————-*/
/* Get the CLOBs */
/*———————-*/
IF NOT dbms_xmldom.isnull(queueNode) THEN
nodeList := dbms_xmldom.getchildnodes(queueNode);
FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP
nodeNode := dbms_xmldom.item(nodeList, nodeIndex -1);
attributeList := dbms_xmldom.getattributes(nodeNode);
CASE dbms_xmldom.getnodename(nodeNode)
WHEN ‘BLOB_TEXT1’ THEN
blob1Node := nodeNode;
WHEN ‘BLOB_TEXT2’ THEN
blob2Node := nodeNode;
ELSE
NULL;
END CASE;
END LOOP;
END IF;
IF NOT dbms_xmldom.isnull(blob1Node) THEN
nodeList := dbms_xmldom.getchildnodes(blob1Node);
FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP
nodeNode := dbms_xmldom.item(nodeList, nodeIndex -1);
IF dbms_xmldom.getnodetype(nodeNode) = dbms_xmldom.CDATA_SECTION_NODE THEN
v_blobText1 := Substr(dbms_xmldom.getnodevalue(nodeNode),1,200);
EXIT;
END IF;
END LOOP;
END IF;
IF NOT dbms_xmldom.isnull(blob2Node) THEN
nodeList := dbms_xmldom.getchildnodes(blob2Node);
FOR nodeIndex IN 1..dbms_xmldom.getlength(nodeList) LOOP
nodeNode := dbms_xmldom.item(nodeList, nodeIndex -1);
IF dbms_xmldom.getnodetype(nodeNode) = dbms_xmldom.CDATA_SECTION_NODE THEN
v_blobText2 := Substr(dbms_xmldom.getnodevalue(nodeNode),1,200);
EXIT;
END IF;
END LOOP;
END IF;
INSERT INTO TRANSACTION_DETAILS(tranID,transDate,transAmt,totalAmt,blobText1,blobText2)
VALUES (v_tranID,v_transDate,v_transAmt,v_totalAmt,v_blobText1,v_blobText2);
errCode := SQLCODE;
errMessage := SUBSTR(SQLERRM, 1 , 64);
RETURN;
END;
The following procedure code is the PostgreSQL equivalent:
CREATE OR REPLACE PROCEDURE public.process_xml
(
xmldocument JSON,
INOUT err_code INTEGER,
INOUT err_message TEXT
)
LANGUAGE ‘plpgsql’
AS $BODY$
DECLARE
jsndocument JSON;
attributelist JSON;
attributeNode JSON;
nodelist JSON;
v_tranID INTEGER;
v_transDate TIMESTAMP;
v_transAmt DOUBLE PRECISION;
v_totalAmt DOUBLE PRECISION;
v_blobText1 VARCHAR(200);
v_blobText2 VARCHAR(200);
BEGIN
attributelist := dbms_xmldom.getattributes(xmldocument, ‘@’);
FOR attributeIndex IN 1..dbms_xmldom.getlength(attributeList) LOOP
attributeNode := dbms_xmldom.item(attributeList, attributeIndex);
CASE dbms_xmldom.getnodename(attributeNode)
WHEN ‘tranID’ THEN
v_tranID := dbms_xmldom.getnodevalue(attributeNode);
WHEN ‘transDate’ THEN
v_transDate := dbms_xmldom.getnodevalue(attributeNode);
WHEN ‘transAmt’ THEN
v_transAmt := dbms_xmldom.getnodevalue(attributeNode);
WHEN ‘totalAmt’ THEN
v_totalAmt := dbms_xmldom.getnodevalue(attributeNode);
ELSE
NULL;
END CASE;
END LOOP;
nodelist := dbms_xmldom.getelementbytagname(xmldocument,’BLOB_TEXT1′);
IF nodelist IS NOT NULL Then
v_blobText1 := Substr(dbms_xmldom.getnodevalue(nodelist),1,200);
END IF;
nodelist := dbms_xmldom.getelementbytagname(xmldocument,’BLOB_TEXT2′);
IF nodelist IS NOT NULL Then
v_blobText2 := Substr(dbms_xmldom.getnodevalue(nodelist),1,200);
END IF;
BEGIN
INSERT INTO TRANSACTION_DETAILS(tranID,transDate,transAmt,totalAmt,
blobText1,blobText2)
VALUES (v_tranID,v_transDate,v_transAmt,v_totalAmt,v_blobText1,v_blobText2);
err_code := 0;
err_message := ‘Record inserted successfully’;
EXCEPTION
WHEN unique_violation THEN
err_code := 23505;
GET STACKED DIAGNOSTICS
err_message := MESSAGE_TEXT;
END;
RETURN;
END;
$BODY$;
Test case 3
In the following Oracle call statement, xmlParams is NULL:
set serveroutput on;
declare
tranID NUMBER := 11011;
transDate DATE := sysdate;
transAmt NUMBER := 999.99;
blobText1 VARCHAR(200) := ‘Credit Card <Refer charges below>’;
blobText2 VARCHAR(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
xmlParams CLOB := NULL;
xmlDocument dbms_xmldom.DOMDocument;
o_errCode NUMBER;
o_errMessage VARCHAR2(64);
begin
Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
Process_XML (xmlDocument,o_errCode,o_errMessage);
DBMS_OUTPUT.PUT_LINE(‘o_errCode:= ‘ || o_errCode || ‘, o_errMessage := ‘ || o_errMessage);
end ;
We get the following output.
The following code is the PostgreSQL equivalent:
DO $$
<<first_block>>
declare tranID integer := 11011;
declare transDate timestamp := current_date;
declare transAmt double precision := 999.99;
declare blobText1 varchar(200) := ‘Credit Card <Refer charges below>’;
declare blobText2 varchar(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
declare xmlParams text := NULL;
declare xmlDocument json;
declare jsndocument json;
declare o_err_code integer;
declare o_err_message text;
BEGIN
call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2,
xmlParams, jsndocument);
call Process_XML (jsndocument, o_err_code,o_err_message);
raise notice ‘o_err_code = %, o_errmessage=%’,o_err_code,o_err_message ;
END first_block $$;
We get the following output.
Test case 4
In the following Oracle call statement, xmlParams contains a value:
set serveroutput on;
declare
tranID NUMBER := 11022;
transDate DATE := ’04-Jul-2022′;
transAmt NUMBER := 888.88;
blobText1 VARCHAR(200) := ‘Credit Card <Refer charges below>’;
blobText2 VARCHAR(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
xmlParams CLOB := ‘<ROOT companyID = “9999” >
<PROCESS_QUEUE totalAmt = “1000”>
</PROCESS_QUEUE>
</ROOT>’;
xmlDocument dbms_xmldom.DOMDocument;
o_errCode NUMBER;
o_errMessage VARCHAR2(64);
begin
Generate_XML (tranID,transDate,transAmt,blobText1,blobText2,xmlParams,xmlDocument);
Process_XML (xmlDocument,o_errCode,o_errMessage);
DBMS_OUTPUT.PUT_LINE(‘o_errCode:= ‘ || o_errCode || ‘, o_errMessage := ‘ || o_errMessage);
end ;
The following screenshot shows our output.
To verify the table record in Oracle, run the following query:
SELECT * FROM TRANSACTION_DETAILS;
You get the following output.
The following call statement is the PostgreSQL equivalent:
DO $$
<<first_block>>
declare tranID integer := 11022;
declare transDate timestamp := ’04-Jul-2022′;
declare transAmt double precision := 888.88;
declare blobText1 varchar(200) := ‘Credit Card <Refer charges below>’;
declare blobText2 varchar(200) := ‘#SBI Bank Charges @2.25%, Others @3.95%’;
declare xmlParams text := ‘{“@companyID”: “9999”,
“PROCESS_QUEUE”: {“@totalAmt”: “1000”}
}’;
declare jsndocument json;
declare o_err_code integer;
declare o_err_message text;
BEGIN
call Generate_XML(tranID, transDate, transAmt, blobText1, blobText2,
xmlParams, jsndocument);
call Process_XML (jsndocument, o_err_code,o_err_message);
raise notice ‘o_err_code = %, o_errmessage=%’,o_err_code,o_err_message ;
END first_block $$;
We get the following output.
Verify the table record in PostgreSQL with the following query:
SELECT * FROM TRANSACTION_DETAILS;
You get the following output.
Conclusion
In this post, we showed you a solution to migrate Oracle’s DBMS_XMLDOM package functions to Aurora PostgreSQL or Amazon RDS for PostgreSQL using the JSON data type.
If you have any questions or comments, share your thoughts in the comment section.
About the Authors
Jitendra Kumar is a Lead Database Migration Consultant with AWS Professional Services. He helps customers migrate and modernize workloads in the AWS Cloud, with a special focus on modern application architectures and development best practices.
Sai Parthasaradhi is a Database Migration Specialist with AWS Professional Services. He works closely with customers to help them migrate and modernize their databases on AWS.
AWS Database Blog
