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:

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.

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