This discussion is archived
9 Replies Latest reply: Jan 30, 2013 4:08 AM by 964809 RSS

'? ; 9/D8@' instead datetime

964809 Newbie
Currently Being Moderated
Hello experts

Please, could you help me.

We have Oracle database 10.2.0.5.0 and dg4odbc to connect to MSSQL 2005.

There is a database link:
create database link old_link
connect to old_user identified by 123
using 'my_sid';
It works well. But we got another ms-user and I created new database link:
create database link new_link
connect to new_user identified by 123
using 'my_sid';
Everything is the same, except user name. New link works. It does not generate any errors. But instead datetime columns it shows something like '? ; 9/D8@'. Other columns are correct.

I am new in all this stuff and have no idea what is wrong. Any suggestions are welcome.
  • 1. Re: '? ; 9/D8@' instead datetime
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    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 ?

    Regards,
    Mike
  • 2. Re: '? ; 9/D8@' instead datetime
    kgronau Guru
    Currently Being Moderated
    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 11.2.0.1 (or any other release) and then it was migrated to 11.2.0.3. 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:
    DECLARE
    cursor c1 is select fds_class_name from hs_fds_class;
    n1 varchar2(30);
    BEGIN
    open c1;
    LOOP
    BEGIN
    fetch c1 into n1;
    dbms_hs.drop_fds_class(n1);
    EXCEPTION
    when others then exit;
    END;
    END LOOP;
    END;
    /
    commit;

    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:
    @$ORACLE_HOME/rdbms/admin/catnohs.sql
    commit;
    @$ORACLE_HOME/rdbms/admin/caths.sql
    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)
  • 3. Re: '? ; 9/D8@' instead datetime
    964809 Newbie
    Currently Being Moderated
    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)
  • 4. Re: '? ; 9/D8@' instead datetime
    kgronau Guru
    Currently Being Moderated
    What happens after you dropped the gateway classes/rebuild the HS catalog as suggested earlier?
  • 5. Re: '? ; 9/D8@' instead datetime
    964809 Newbie
    Currently Being Moderated
    kgronau,

    I tried both suggestions and unfortunately they did not help. The results of selects are the same.
  • 6. Re: '? ; 9/D8@' instead datetime
    kgronau Guru
    Currently Being Moderated
    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.
  • 7. Re: '? ; 9/D8@' instead datetime
    964809 Newbie
    Currently Being Moderated
    kgronau, thank you for your clear explanation.

    I had HS_FDS_TRACE_LEVEL=ON in the gateway init file.
    Then I executed
    select * from "##R_temp"@old_link;
    select * from "##R_temp"@new_link;
    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:
    hgoulcp, line 1107: calling SQLGetTypeInfo got sqlstate 23000
    Does it make sense?

    I am sorry but I can not upload the files to a public file hosting server.
  • 8. Re: '? ; 9/D8@' instead datetime
    kgronau Guru
    Currently Being Moderated
    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.
  • 9. Re: '? ; 9/D8@' instead datetime
    964809 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points