9 Replies Latest reply: Jan 30, 2013 6:08 AM by Nyirka RSS

    '? ; 9/D8@' instead datetime

    Nyirka
      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
          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-Oracle
            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
              Nyirka
              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-Oracle
                What happens after you dropped the gateway classes/rebuild the HS catalog as suggested earlier?
                • 5. Re: '? ; 9/D8@' instead datetime
                  Nyirka
                  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-Oracle
                    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
                      Nyirka
                      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-Oracle
                        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
                          Nyirka
                          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.