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')
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply