November 19, 2020 at 10:11 pm
William - to address both replies (Jeffery and sterling3721, correct me if I am misrepresenting):
1 - the new driver MAY have some optimizations in it that cause it to give odd results when certain characters are present. It could be that in most cases, these characters are not present so Oracle decided to handle things differently in the upgraded driver. Alternately, it could be some additional configuration options or some defaults were changed in the driver in version 12.2.0.1. It COULD be that when you are using 12.2, you need to provide some other options to handle things differently on special characters. Mind you, this is just a guess.
2 - as for opening a support ticket with Microsoft for the Oracle driver used in SSIS causing large memory consumption, that could be an SSIS bug or it could be a bug with the Oracle driver, or again, it could be a configuration or parameter issue.
William - as per the problem that you are having, I am wondering if it is a bug in the implementation of "Support is added for long identifiers." as that seems to be one of the two documented features added in that update. It could be something simple like changing a driver configuration option (changing the numeric settings for example to one that is more compatible than the default). There appear to be a lot of different options you can play with according to the Oracle docs, so may not hurt to load things up on test and experiment. I do not have any Oracle instances to play with unfortunately.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 19, 2020 at 10:50 pm
As for ODAC 12.2 memory leak, Oracle said there was a fix, but it required a service account to download. Since this was not my major area, I just used ODAC 12.1 driver on the same box and it worked fine with no issue so far.
November 20, 2020 at 9:14 am
Wouldn't the possibility of this happening due to some embedded characters be ruled out due to the fact it also happens with binary data? Oracle BLOB in my first example?
Regarding the configuration and defaults in 12.2 > , with Oracle support I went over linked server /provider parameters (Dynamic Parameters, Allow InProcess, Collation Compatible), all were set as documented and same between driver verisons. There is not much else to configure on driver level.
@william, No I didn't log a ticket at MS.
November 20, 2020 at 1:16 pm
>Regarding the configuration and defaults in 12.2 > , with Oracle support I went over linked server /provider parameters (Dynamic Parameters, Allow InProcess, Collation Compatible), all were set as documented and same between driver verisons.
I remember when we built out our new servers and sql versions, we had to turn inProcess on, and a few other things. This fixed some of our issues but not the bad results.
I believe we tried both the newer 12.x version and also a 19.x version. Both were causing problems.
An "enhancement" which never good booted out of MS's code? 8)
Good luck! This whole thing p**ved me off pretty good....so I'm watching this thread.
P.S. Our current src/tgt are Oracle 19c on a linux box, and SQL Server version 17/19 on Windows boxes.
September 17, 2021 at 11:40 am
This works for me on Oracle 19c using a linked server on SQL 2019:
SELECT * FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT CAST(DBMS_LOB.SUBSTR(FIELD_NAME, 4000,1) as VARCHAR2(4000)) as DATA_CLOB,
CAST(DBMS_LOB.SUBSTR(FIELD_NAME, 4000,4001) as VARCHAR2(4000)) DATA_CLOB2,
CAST(DBMS_LOB.SUBSTR(FIELD_NAME, 4000,8001) as VARCHAR2(4000)) as DATA_CLOB3,
CAST(DBMS_LOB.SUBSTR(FIELD_NAME, 4000,12001) as VARCHAR2(4000)) as DATA_CLOB4,
CAST(DBMS_LOB.SUBSTR(FIELD_NAME, 4000,16001) as VARCHAR2(4000)) AS DATA_CLOB5
FROM TABLENAME')
March 29, 2023 at 3:32 pm
Hi All,
A big thanks to all the contributors to this thread. I confirm that rolling back to the 12.1 OLEDB drivers does resolve the issue.
However if you need to import CLOB files this is the method that I got to work for 12.2
The principle was:
This is slow, but does resolve the issue. I couldn't use OPENQUERY as it requires dynamic SQL. However this does correctly copy the data accross!
I hope this helps someone as I have spent a few hours trying to resolve this!
DELETE FROM [etl].[tblNotes];
GO
PRINT '** OUTPUT FOR NOTES: **'
Declare @preCount INT;
SELECT @preCount = COUNT(*) FROM [SocratesOrclDB]..CME.TBLEXPANDABLENOTES as nts LEFT OUTER JOIN [SocratesOrclDB]..CME.XSECURITYUSERS AS urs ON nts.addedby = urs.id LEFT OUTER JOIN [SocratesOrclDB]..CME.TBLSTANDINGDATA AS stf ON urs.staffid = stf.dataid
PRINT 'CME.TBLEXPANDABLENOTES Count - ' + STR(@preCount)
-- Insert main data (without Notes as this has a bug in the OraOLEDB 12.2 version!
INSERT INTO [etl].[tblNotes] ( [Notes_Id], [Notes_Size], [Added_By_User], [Added_By_Name], [Added_Date] )
SELECT * FROM OPENQUERY([SocratesOrclDB], '
SELECT nts.notesId, nts.notessize, urs.Name, stf.value, CASE WHEN addedwhen < TO_DATE(''17530101'',''YYYYMMDD'') THEN NULL ELSE addedwhen END
FROM CME.tblexpandableNotes nts
LEFT OUTER JOIN CME.xsecurityusers urs ON nts.addedby = urs.id
LEFT OUTER JOIN CME.tblstandingdata stf ON urs.staffid = stf.dataid
');
-- Post Transformation Count
Declare @postCount INT;
SELECT @postCount = COUNT(*) FROM [ETL].[tblNotes];
PRINT 'ETL.tblNotes Count - ' + STR(@postCount)
GO
PRINT '** OUTPUT FOR NOTES: **'
/* ======================================================
Go through each record and udpate the notes!
=======================================================*/
Declare @notesid BIGINT;
Declare @sql NVARCHAR(MAX);
Declare noteCursor CURSOR FOR SELECT Notes_Id FROM [etl].[tblnotes];
OPEN noteCursor
FETCH NEXT FROM noteCursor INTO@notesid;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
SET @sql = N'SELECT NOTES FROM OPENQUERY([SocratesOrclDB], ''SELECT nts.Notes FROM CME.tblexpandableNotes nts WHERE nts.NotesId = ' + CONVERT(varchar(10), @notesid) + ''')';
-- Update the Notes from a single entry! We cannot use OPENQUERY as this does not support dynamic SQL
UPDATE [etl].[TblNotes] SET Notes = (SELECT Notes FROM [SocratesOrclDB]..CME.TBLEXPANDABLENOTES WHERE NotesId = @notesId)
WHERE Notes_Id = @notesId
COMMIT;
FETCH NEXT FROM noteCursor INTO@notesid;
END;
CLOSE noteCursor;
deallocate noteCursor;
GO
March 29, 2023 at 3:32 pm
This was removed by the editor as SPAM
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply