1 Reply Latest reply on Apr 4, 2007 5:48 PM by Barry Mcgillin-Oracle

    Trailing spaces isssues in VARCHAR2

    557703
      I've recently migrated few Sybase ASE12 databases to Oracle10g R2 Using Oracle Migration workbench release 10.1.0.4.0.

      Now there is some problem with Trailing spaces char in Varchar2 datatypes in oracle.


      During migration I've mapped all sybase " char" datatypes to Oracle VARCHAR2 datatypes.Migration is done successfuly Now when i checked the data for verification i found that Oracle
      insert/append space( after last char) character in all the char field wherever
      inserted values in that columns is short of actual field length.

      for example

      I have a table name "XYZ" in Sybase with following columns:

      Name char(8)
      Age int
      for example

      I have a table name "TEST" in Sybase with following columns:

      Name char(8)
      Age int

      insert into TEST values ('AMITGOE',27)
      insert into TEST values ('ABCDEFG',20)
      insert into TEST values ('ABCDEFG',20)
      insert into TEST values ('XYZJKH',22)

      Once i migrated it to Oracle , table in oracle looks as follows:

      SQL> desc TEST

      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      NAME NOT NULL VARCHAR2(8)
      AGE NOT NULL NUMBER(10)

      SQL> select * from test;

      NAME AGE
      -------- ----------
      AMITGOE 27
      ABCDEFG 20
      ABCDEFG 20
      XYZJKH 22

      When i issue following query it gives no result , while idealy it should return one row,

      SQL> select * from ons where NAME='XYZJKH' ;

      no rows selected

      When i modified it and added " TWO space character" in the last of where clause it return the result ( as 8 character field contains only 6 char and after adding two space it make 8 characters so it returns the row)


      similarly in other case

      SQL> select * from ons where NAME='AMITGOE' ;

      no rows selected

      When i added one space char it return the result ( as 7 character + 1 space make it 8 character)

      SQL> select * from ons where NAME='AMITGOE ' ;

      NAME AGE
      -------- ----------
      AMITGOE 27



      When i migrated and converted Sybase "Char" datatype to Oracle "Char" datatype it works well and i faced no issue.

      Can yanyone let me know why its happening and any work around from this situation.

      Early reply will be appreciated.