Using OpenQuery with Fields Larger than 4000 characters

[Jberry1]

I'm trying to fetch syslog records for DynamicSchedulingLog into my ms sql server using openquery.    The message field on these type of records is usually over 4000 characters, and no matter what I try I cannot get oracle to behave and give me the full message log.  My data fields that I'm inserting into is NVARCHAR(MAX).  

I've even tried breaking the message into multiple 4000 character chucks on the orcale side of the openquery but I still get no results except for the first piece. 

I'm even trying to parse it on the Oracle side but it results in only the first part full to 4000 characters and the other two fields blank:

   SUBSTR(message, 1, 4000) as message_part1,
   SUBSTR(message, 4001, 4000) as message_part2,
   SUBSTR(message, 8001, 4000) as message_part3,

This is my current query:

DECLARE @LatestUpdate DATETIME = '2024-08-16 09:00:00';
DECLARE @Command NVARCHAR(MAX);

-- Oracle query
SET @Command = N'
SELECT TOP 100
sys_id,
SUBSTR(message, 1, 4000) as message_part1,
SUBSTR(message, 4001, 4000) as message_part2,
SUBSTR(message, 8001, 4000) as message_part3,
source,
sys_created_on
FROM syslog
WHERE sys_created_on > ''' + CONVERT(NVARCHAR(100), @LatestUpdate, 21) + '''
AND message LIKE ''%wot%''
AND source LIKE ''DynamicSchedulingAjax''
ORDER BY sys_created_on';

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
INSERT INTO #TempSyslog (sys_id, message_part1, message_part2, message_part3, source, sys_created_on)
SELECT top 100 * FROM OPENQUERY(SERVICENOW, ''' + REPLACE(@Command, '''', '''''') + ''')';

EXEC sp_executesql @SQL;

Can anyone give me some tips on how to get oracle to feed me the full message from the syslog no matter the length?  I've experimented with this too which fails:

DBMS_LOB.SUBSTR(message, 4000, 1) as message_part1,

DBMS_LOB.SUBSTR(message, 4000, 4001) as message_part2,

DBMS_LOB.SUBSTR(message, 4000, 8001) as message_part3

Thanks for any guidance.

[Jberry1]

anyone have any thoughts on this?