SQL Table Query with CLOBs

Products

• CA Release Automation - Release Operations Center (Nolio)
• CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

Problem:

 

We have configured an "Execute SQL Query on Oracle(c) Database Server" action as follows:

Input field: "SQL Query To Execute" 

• select * from tablename; or
• select varcharColumnName, varcharColumnName, clobColumnName from tablename;

 

Output field: "Result Set [Object[][]]" to use a parameter of type ResultSet.

 

Result: When we execute a query we see it returns the following to this parameter:

[email protected]], [[email protected]], [[email protected]], [[email protected]], [[email protected]

 

Cause:

These values are returned when it queries a table that a column with the data type of CLOB. Clob data can be very big (Gigabytes). Many SQL tools apply some kind of "preview" mode for clob columns and also let the user configure how many characters to display in the UI for clob columns. In order to see all of the data in tools that offer the same "preview" mode you would need to fine tune the query so that it can capture/return/display the data accordingly. One way to do this is by using the TO_CHAR.

 

Resolution:

 

The query used by the Release Automation action needs to be fine tuned the same way 3rd party tools (without preview mode) need to be tuned. It is recommended to consult your DBA to come up with an appropriate query. Instead of the same query above:

select varcharColumnName, varcharColumnName, clobColumnName from tablename;

You might have a query similar to:

select varcharColumnName, varcharColumnName, TO_CHAR(clobColumnName) from tablename;

 

Additional Information:

 

The TO_CHAR procedure was mentioned earlier. If you decide to use this then please note that care needs to be taken when trying to convert the clob data to varchar2 specifically when a CLOB record has more than 4000 bytes of data. This is true because 4000 bytes is the max size of the varchar2 datatype.

Environment

Release:
Component: RACORE