TO_CLOB() SQL Function

See Also

The TO_CLOB() function is supported by Oracle 9 and higher. This function converts an expression into a CLOB data type. Oracle 8 does not support this function.

However, when running against Oracle 8 or another database, the TO_CLOB() function can be used when calling a component that returns a large string, for example:

SELECT TO_CLOB(MyComponent.FetchLongString(MyParameter))

FROM MyTable

This statement is handled completely by USoft. On databases other than Oracle 9, the TO_CLOB() function is stripped from the database statement.

Background:

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.

NOTE:

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 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 t1