1 Reply Latest reply on Aug 14, 2011 2:38 PM by clcarter

    Getting data from Firebird

      I'm working in a project which I need to transfer some data from a firebird database to an oracle database. I'm using ODBC and Oracle Heterogeneous Services to connect in firebird from pl/sql procedures and functions.
      When I'm reading numeric fields, the data inside these fields is returned as integer. (e.g. 812,43 returns as 812). The only way I can read the data correctly is dividing it by 100 and multiplying by 100 again. But when I need to use these one in a function(e.g. SUM), it returns as integer again, even when I perform the divide/multiply.

      Does anyone know how can I figure out it?

      Best regards
      Everton Lucas
        • 1. Re: Getting data from Firebird
          What's the locale for the data source and target? (Language and territory)

          If its perhaps something European where 812,14 is equivalent to (i.e. AMERICAN_AMERICA) 812.34 might have a glitch on the nls parameters for the oracle session.

          The numeric data types may not be correct for storing fractional values, i.e. a NUMBER(38) column will only store integer values. If there is no scale, i.e. the data type declaration is just NUMBER then fraction values won't get "lost" when stored in the table.

          The 11g globalization guide, chapter "Setting Up a Globalization Support Environment" can be quite helpful if your looking at pulling data from a locale that is different from the oracle database:

          There are several ways to set a different locale, it can be done via setting an environment variable at the client, adjusting database instance parameters, or just within a session, i.e.:
          alter session set NLS_NUMERIC_CHARACTERS = ',.';
          And ODBC and the Heterogeneous Services setups can get tricky if you are migrating to/from different locales as well. If there is any way to get the data to a flat file, there are several ways to get the data into the oracle database that work around those possible issues as well.
          1 person found this helpful