In SQL-Toolkit plugin , certain datatypes like BLOB, CLOB and Long Binary are unsupported. If an SQL query returned these datatypes, the Active Console may display a message similar to the below:
Users are suggested to rewrite the query with SQL functions that convert the data to readable formats. We have provided an example in this article for CLOB datatype. Users should consult the relevant database documentations as appropriate.
- In this example, the column named "COL2" is of CLOB datatype in an Oracle database. We will use the CAST and SUBSTR functions for the conversion. The purpose of SUBSTR function is to trim down the output to 1000 characters should the source data is huge.
- This is the SQL query that we use:
SELECT col1, cast(substr(col2,1,1000) as varchar(1000)) AS col2 FROM clob_table
The Active Console output is provided below.
Note : INTERVAL datatype
|MySQL / Maria DB||
SQL-Toolkit plugin currently does not support the INTERVAL datatype specific to MySQL databases. The following error may appear on the Active Console.
If user is trying to use MySQL's SEC_TO_TIME function, the SUBSTRING function can be combined to make the string conversion. For example,
SUBSTRING(SEC_TO_TIME(t1.start_time),1,8) as "Logged Time"
Some users on older Netprobe versions reported encountering problems with Oracle INTERVAL datatype. The following error message was returned.
ORA-01406: fetched column value was truncated
We have tested the INTERVAL datatype (INTERVAL YEAR TO MONTH / INTERVAL DAY TO SECOND) to work in Geneos versions GA3.6 and GA4.x. Users are advised to upgrade the Netprobe to newer releases.