SQL & PL/SQL

We are experiencing long string truncation when copying SQL Server records to Oracle tables usig a PL/SQL procedure via a Gateway. The source strings happen to be XML strings, but they are stored in SQL Server as standard text strings (i.e. theoretically this could be any string).

Currently the procedure uses solution recommended in tech note: 974112.1 (quoted below). Because of limitations INSERT INTO AS SELECT, and SQLPLUS COPY are not an option.
The source SQL column is a 'TEXT' datatype and it is moved using a PL/SQL LONG and inserted into an Oracle CLOB column. We are running into a truncation issue due to PL/SQL LONG limitations (tech note 463115.1) - 32760 bytes. I need to find a solution that allows most direct copy of the full text long strings and to move away from using deprecated datatype, which we now have at both sides of the Gateway (TEXT in MS SQL Server and LONG in Oracle PL/SQL).

We are considering changing the source datatype. Does anybody know of datatype match on the SQL Server, other than TEXT, and in Oracle PL/SQL that would allow for a direct copy of the compelete string into an Oracle datatype (e.g. CLOB or XMLTYPE) with the use of Oracle's SQL Gateway.

Thanks,
Julian

How to Copy Non-Oracle Data That Maps as Oracle's LONG Datatype [ID 974112.1]

DECLARE
V1 VARCHAR2(50);
V2 LONG;
CURSOR copycursor IS SELECT * FROM longtest@hsodbc;


BEGIN
OPEN copycursor;
LOOP
FETCH copycursor INTO v1,V2;
EXIT WHEN copycursor%NOTFOUND;
INSERT INTO longtest VALUES (V1,V2);
END LOOP;


CLOSE copycursor;
END;


/