TO_CLOB SQL Function

Converts a value to CLOB data type.

Syntax

TO_CLOB( value )

Background

This is a convertible function resolved by USoft but NOT supported on MS Sql Server, where you must use CONVERT.

Large strings (>32K) returned by component methods are truncated when used in other SQL statements. If the method call is called directly within an INVOKE statement, for example:

INVOKE MyComponent.FetchLongString

then there is no problem returning the large string. But as soon as the call is incorporated in a SQL statement, the value will be silently truncated to 32K, the USoft limit for long strings. For example:

SELECT MyComponent.FetchLongString()
FROM MyTable

You can use the TO_CLOB() function to return the long string correctly without it being truncated.

The TO_CLOB() function must be positioned directly around the method call, otherwise truncation might occur before the TO_CLOB() function can do the conversion. For example, the following statement still truncates the value:

SELECT TO_CLOB( 'Test' || MyComp.MyMethod( col ))
FROM MyTable t1

To ensure that no truncation occurs, the TO_CLOB() function must be positioned directly around the method call:

SELECT 'Test' || TO_CLOB( MyComp.MyMethod( col ))
FROM MyTable t1