The source of this issue lies in a property setting in the EnsLib.SQL.CommonJ class, which gets extended by the EnsLib.SQL.OutboundAdapter.

Class EnsLib.SQL.CommonJ Extends EnsLib.JavaGateway.Common [ Abstract, ClassType = "", ProcedureBlock, System = 4 ]
{
.
.
.

/// When greater than this length of characters a VARCHAR column is
/// to be treated as a LOB and stored in a stream. <br>
/// The default is 32767. <br>
/// Use -1 to use the system maximum string length of 3641144. <br>
/// Note the system maximum string length may change in future versions
/// and therefore the value used when -1 is selected can change. <br>
/// If the value entered is greater than the maximum system string length
/// then the maximum system string length will be used. <br><br>
/// This setting can be used for example when 'text' columns on the 
/// target system cannot be retrieved as LOBs and it is necessary to 
/// CAST the column as a VARCHAR longer than 32767. <br> 
Property MaxVarCharLengthAsString As %Integer [ InitialExpression = 32767 ];

If the column you are pulling data from is larger than varchar(32767),  the data will be treated as a LOB.

There are a couple of workarounds.  

If you are working with a database column configured to be larger than varchar(32767), but you're confident the data length in your query result for that column will be less than 32767, you can cast the column in your select statement as follows:

select cast(column_name as varchar(32767)) from table;

A more robust solution is to modify the MaxVarCharLengthAsString property in the Adapter.  The following worked for me - I was able to pull a varchar(65535) column from Redshift without casting.

Class Example.Redshift.MyGenericOperation Extends EnsLib.SQL.Operation.GenericOperation
{

Method OnMessage(pRequest As Ens.Request, Output pResponse As Ens.Response) As %Status
{
    set ..Adapter.MaxVarCharLengthAsString = -1
    set tSC = ##class(EnsLib.SQL.Operation.GenericOperation)$this.OnMessage(pRequest,.pResponse)
    return tSC
}

}

Fair warning - I'm new to Intersystems, so I don't know if there are better ways to do this or if there are any hidden problems with what I proposed.  Input from anyone who has suggestions to improve this approach would be appreciated!

Sorry, I haven't found a workaround yet. 

Was this issue ever resolved?  I'm experiencing the same thing when trying to read an Amazon Redshift column set up as varchar(65535) from an Enslib.SQL.OperationGenericOperation in a production.  I can read any of the other columns in the table, but when I try to read the varchar(65535) column, I get the following error:

An error was received : ERROR #5023: Remote Gateway Error: JDBC Gateway getClob(0,1) errorRemote JDBC error: Cannot convert the column of type VARCHAR to requested type long..

I know Redshift is similar to postgresql and doesn't support CLOBs, but hoping there is a workaround.  As Joost stated, casting the column to a different data type doesn't seem to work.