This content has been marked as final. Show 9 replies
Are you saying that old_user can select the datetime column successfully but the new_user gets the wrong result ?
Are they bot selecting fromthe same table ? What is the SSQL*Server create statement for the table in the select ?
Was the Oracle database migrated to a newer release shortly?
This phenomena was happening when the gateway was used for example with the Oracle database 18.104.22.168 (or any other release) and then it was migrated to 22.214.171.124. Since then the date was shown corrupted with existing heterogeneous database links.
The solution is to drop the existing gateway classes using this piece of code being connected as sysdba user:
cursor c1 is select fds_class_name from hs_fds_class;
fetch c1 into n1;
when others then exit;
It will drop all gateway classes in the database. Next time a database link based on the gateway is used, the agent will detect that there's no class registered in the database and will upload its capabilities again. In some very rare cases dropping the gateway classes wasn't enough. The whole catalog had to be rebuilt:
connect with SQL*Plus as sysdba user
execute the scripts followed by a commit:
Then use the gateway and the gateway will again upload its capabilities into the Oracle database.
Edited by: kgronau on Sep 27, 2012 3:55 PM
More details can be found in the Oracle note:
After Migrating an Oracle Database From 9.2, 10.1 or 10.2 to 11.2 Heterogeneous Gateway Connections using 11.2 Gateway Can Show Strange Data When The DB Link Already Exists Prior Migration (Doc ID 1203633.1)
Mike, thank you for your reply
Are you saying that old_user can select the datetime column successfully but the new_user gets the wrong result ?Yes, this is correct.
Are they bot selecting fromthe same table ? What is the SSQL*Server create statement for the table in the select ?They bouth select from the same table.
create table ##R_temp (dttm_column datetime)
What happens after you dropped the gateway classes/rebuild the HS catalog as suggested earlier?
I tried both suggestions and unfortunately they did not help. The results of selects are the same.
Could you please enable gateway tracing and provide a trace for both users?
Gateway tracing is enabled by setting in the gateway init file HS_FDS_TRACE_LEVEL=255
Best would be to upload the files to a public file hosting server so I can download them to check them out.
Edited by: kgronau on Sep 28, 2012 10:40 AM
Maybe I should also mention.... when you upload the file to a public file hosting server and provide the link, everybody will be able to check out this file, too. If you don't want to share any information, then please log a service request at Oracle support.
kgronau, thank you for your clear explanation.
I had HS_FDS_TRACE_LEVEL=ON in the gateway init file.
Then I executed
I got two *.trc files and compared them by content. There is only one difference between the files. In the trace file for new_user there is a line:
select * from "##R_temp"@old_link; select * from "##R_temp"@new_link;
Does it make sense?
hgoulcp, line 1107: calling SQLGetTypeInfo got sqlstate 23000
I am sorry but I can not upload the files to a public file hosting server.
It makes sense as SQLGetTypeInfo is responsible for the data type mapping and every select being executed is first trying to get the info about the columns and data types stored in the foreign database.
When you can't publish the file I think it would be best to engage Oracle support and upload those trace files to the service request ticket.
Thank you for your help!
The described problem appeared when MSSQL user was granted extra privileges on dbo schema. We compared list of privileges befor the problem and after. Then revoke extra privileges. It helped.
Unfortunatly I can not explain it more clear. This bug is still mistery for me.