1 Reply Latest reply on Feb 10, 2021 8:36 AM by Karthik Raja V

    How to archive the data from Oracle database where datatype of the columns are varchar2(100 char), char(1 char)

    Shyam Baggu New Member

      While archiving we may have error as "java.lang.Exception: Exception occurred while processing the table, Caused by java.lang.Exception: ERROR: Invalid Query" or if you analyze further and analyze the staging file you will find the actual error as "Too Long Value". If you check the EDM the data types are as defined in the source tables.

      The reason is because of the data within the table and datatype defined. if the column datatype is VARCHAR2(100), Oracle will allocate 100 bytes for storage, but user may not actually be able to store 100 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

      If the column datatype is VARCHAR2(100 CHAR) the Oracle allocates enough space to store 100 characters, no matter how many bytes it takes to store each one. These features are available from Oracle 10g where 3 bytes per character were used.

       

      So to archive such tables it's always suggested to create virtual view and change the data type as multiples of 3 defined for the column. e.g. if the column datatype is varchar2(100 char), then it's recommended in the virtual view the data type of the column modified as varchar2(300).

        This content has been marked as final.  Show 1 reply