How to access "CLOB" data from database

I am trying to get an XML that is stored in a column called “XML_DATA” in a table with a CLOB data type. This is being done using the Data Files Test data type - Database data.

Connection - jdbc:oracle:thin:@//organisation.com:portnumber/XXXX

Query is below -
SELECT T.XX, T.XXX, T.XML_DATA FROM TABLE_NAME T
WHERE T.REC_ID = ‘xxx’
AND T.TRANSACTION_TYPE=‘xxx’
AND INITIATED_BY_HOST=‘x’
ORDER BY T.DATE_TIME DESC

I then retrieve that value in my test case and am trying to convert it to a string as -

DBData XxxData = findTestData(‘Database/XXXXX_XMLData’)
XMLValue = XxxData.getValue(3, 1)
String xml = CustomKeywords.‘parseXML.StringXML.ClobtoString’(XMLValue)

I am using a custom keyword that converts the CLOB to a String
the Clob as can be seen below is -
oracle.sql.CLOB@4ba89729

I get an error at this point as -

2020-05-27 16:21:37.826 DEBUG testcase.CompareXML - 1: XMLValue = XxxData.getValue(3, 1)
2020-05-27 16:21:37.827 DEBUG testcase.CompareXML - 2: println(XMLValue)
oracle.sql.CLOB@4ba89729
2020-05-27 16:21:37.829 DEBUG testcase.CompareXML - 3: xml = parseXML.StringXML.ClobtoString(XMLValue)
2020-05-27 16:21:37.980 ERROR k.k.c.m.CustomKeywordDelegatingMetaClass - :x: No signature of method: parseXML.StringXML.ClobtoString() is applicable for argument types: (java.lang.String) values: [oracle.sql.CLOB@4ba89729]
Possible solutions: toString(), toString()
2020-05-27 16:21:37.984 ERROR c.k.katalon.core.main.TestCaseExecutor - :x: Test Cases/Regression/XmlComparison/CompareXML FAILED.
Reason:
org.codehaus.groovy.runtime.metaclass.MissingMethodExceptionNoStack: No signature of method: parseXML.StringXML.ClobtoString() is applicable for argument types: (java.lang.String) values: [oracle.sql.CLOB@4ba89729]
Possible solutions: toString(), toString()

2020-05-27 16:21:37.993 INFO c.k.katalon.core.main.TestCaseExecutor - END Test Cases/Regression/XmlComparison/CompareXML

What am I doing wrong? Why does it think that the value of the XML is a string when it shows that it is a Clob? Please advise how I can handle this scenario?

Thank you!

Try inserting a few debug print like this:

DBData XxxData = findTestData('Database/XXXXX_XMLData')
// debug print
WebUI.comment("XssData is an instance of ${XxxData.getClass().getName()}")
XMLValue = XxxData.getValue(3, 1)
// debug print
WebUI.comment("XMLValue is an instance of ${XMLValue.getClass().getName()}")
String xml = CustomKeywords.'parseXML.StringXML.ClobtoString'(XMLValue)

I suppose you would see

XMLValue is an instance of java.lang.String

this would be a small surprise for you.

This is the reason why you got an error of
2020-05-27 16:21:37.980 ERROR k.k.c.m.CustomKeywordDelegatingMetaClass - :x: No signature of method: parseXML.StringXML.ClobtoString() is applicable for argument types: (java.lang.String)

Possibly you should just write

String xml = XxxData.getValue(3, 1)

You don’t need to call CustomKeywords.'parseXML.StringXML.ClobtoString'(...)

@kazurayam Thank you for your response.
I tried what you recommended and what I see is below. You were right, it is DBdata and String.

Why does Katalon think that it is a string when it is a CLOB? I need the XML that is stored in the database as a CLOB data type. How can I achieve that?

Why do you need a CLOB data type here?

CLOB (Character Large OBject) is a data type used only inside a Database Management System. It is not a universal data type. See https://en.wikipedia.org/wiki/Character_large_object

In a script in Groovy language, a XML document can be stored in a variable of type java.lang.String. And Groovy language provides enough tools to parse a XML document and to get access to the contents.

How to process a XML in Groovy? See

My apologies if I was not clear earlier. The XML that I need for my test is stored in the database and when I query that column using Katalon , it returns this - oracle.sql.CLOB@141e879d
I am not using a API request to get the XML…this is an XML that is sent to our organization and gets stored in the database. That is why I started looking into converting this Clob data into an XML

Dit you expect Katalon Studio does extract the XML from CLOB data in Oracle and convert it into a String variable in Groovy script intelligently and silently?

@ThanhTo

Does KS do it?

It makes sense now that Katalon does not support CLOB. I will try to incorporate the XML parsing inside the query before the column data is returned in Katalon

Thank you for your help

1 Like