2 Replies Latest reply: Sep 7, 2012 5:54 AM by Pnreddy RSS

    Is this a bug in OWB 11.2 - importing table metadata for character columns

      The Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide provides an overview of the data types supported.


      It says that for VARCHAR2 data type it saws (http://docs.oracle.com/cd/E11882_01/owb.112/e10935/orcl_data_objx.htm#CHDFIADI )

      "Stores variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 data type takes a required parameter that specifies a maximum size up to 4,000 characters"

      That means , I guess, it says that when I import a table, any columns of type VARCHAR2(10) in the database should have its length show as characters in OWB, so a column of type Varchar2(10) in the Oracle database, should be shown as Varchar2(10) when imported into OWB table metadata via the OWB import function.

      However, if I have a database that set-up as a single-byte and import a table using the OWB import function a column that has a size of e.g. 10 in the database, is imported as OWB table metadata and the size is 10. Correct, I am happy.

      However, if the database is modified to support multi-byte characters, ALTUF16 encoding with the semantics set to "CHAR", then when I import the same table into OWB, OWB reports the size as 40, I guess its 40 bytes as in 10 characters @ 4 bytes per character.

      Is this a bug in OWB, as the datatype in the Oracle DB is varchar2(10), should OWB after importing a table not also report the column as VARCHAR2(10) ? Currently, is shows the column as varchar2(40).
        • 1. Re: Is this a bug in OWB 11.2 - importing table metadata for character columns
          BRZ-DWH Steuer
          I noticed that myself in our project.
          Our varchars2 are defined as VARCHAR2(xxx CHAR) - OWB puts the size*4

          In fact if you have special characters like umlauts (ü,ä,ö,...) it will use 4 bytes per character.
          You can try it yourself. Define a Varchar2(1 CHAR) and manually change the size of the Column in your mapping inside OWB (in filters, joins or your target table).

          Then shoot an umlaut through the mapping and will end up with a "too small" error.

          Dont mind the size*4 issue - we totally ignored it and run without error since 4 years now.
          • 2. Re: Is this a bug in OWB 11.2 - importing table metadata for character columns

            I think this may not be a bug, but only thing is OWB is always displaying the size in bytes.

            Example we have some fields in our tables whcih are of VARCHAR2(256 CHAR)(with each character of 4 bytes), but after importing them to OWB it shows as VARCHAR2(1024) which is still correct if we convert it to bytes.

            But anyway you can check with Oracle Support for confirmation.