TO_CLOB() SQL Function
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:
This statement is handled completely by USoft. On databases other than Oracle 9, the TO_CLOB() function is stripped from the database statement.
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:
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 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