7 Replies Latest reply: Oct 1, 2013 1:03 PM by mkeskin RSS

    Binary to character conversion problem

    Raluce

      Hi all,

       

      I have a problem in canverting some character column in my database.

      I have exported one table from one database Windows Oracle 10g which contains one column type of varchar2 but the content is an ENCRYPTED BINARY (like for example "bp0  EBo"), in this source database we are using function called RAWTOHEX to get the format of this colum in hexadecimal and it is working properly.

      The problem is we were importing this tables in a new Oracle database 11gR1 (Linux Debian 6) and using the same Query but in this new database we don't get decimal formatlike below:

       

      select rowtohex(msisdn) from su_data;

      24055122BF10FFFF

       

      Note that during importation we got a duplicate value error in indexe creation which may be caused by incoherence of CHARACTER SET of the two database.

       

      import server uses WE8MSWIN1252 character set (possible charset conversion)

      export client uses WE8ISO8859P1 character set (possible charset conversion)

       

      The Source database character set is WE8ISO8859P1 and the destination is WE8MSWIN1252.

       

      Could you please help in how to solve this problem, can set change character set during importation only or i have to change database character set.

      Can i do the conversion from Oracel Query like using conversion function, if yes which function is the best ?

       

      Ragards,

       

      Raluce.

        • 2. Re: Binary to character conversion problem
          Raluce

          Hi

          I do the same thing as recommanded in the link you sent,i changed CLIENT nls lang environment variable to AMERICAN_AMERICA.WE8ISO8859P1 before importing but still the same result and still having duplicate key error

           

          $export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

           

          import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

          import server uses WE8MSWIN1252 character set (possible charset conversion)

          . importing HLR's objects into DATAWH

          . . importing table                     "SUB_DATA"

          .........................

                                                                 254699 rows imported

          IMP-00017: following statement failed with ORACLE error 1452:

          "CREATE UNIQUE INDEX "XPKSUB_DATA" ON "SUB_DATA" ("IMSI" )  PCTFREE 5 INITRA"

          "NS 30 MAXTRANS 255 STORAGE(INITIAL 2097152 FREELISTS 1 FREELIST GROUPS 1) T"

          "ABLESPACE "HLRTS" LOGGING"

          IMP-00003: ORACLE error 1452 encountered

          ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

          IMP-00017: following statement failed with ORACLE error 1452:

          "CREATE UNIQUE INDEX "XAK1SUB_DATA" ON "SUB_DATA" ("MSISDN" )  PCTFREE 5 INI"

          "TRANS 30 MAXTRANS 255 STORAGE(INITIAL 2097152 FREELISTS 1 FREELIST GROUPS 1"

          ") TABLESPACE "HLRTS" LOGGING"

          IMP-00003: ORACLE error 1452 encountered

          ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

          IMP-00017: following statement failed with ORACLE error 1452:

          "CREATE UNIQUE INDEX "XPK2SUB_DATA" ON "SUB_DATA" ("HLR_INDEX" , "IMSI" )  P"

          "CTFREE 5 INITRANS 30 MAXTRANS 255 STORAGE(INITIAL 2097152 FREELISTS 1 FREEL"

          "IST GROUPS 1) TABLESPACE "HLRTS" LOGGING"

          IMP-00003: ORACLE error 1452 encountered

          ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

          Import terminated successfully with warnings.

           

          Thank you.

           

          Raluce

          • 3. Re: Binary to character conversion problem
            mkeskin

            Hi ,

             

            This issue not related with NLS_LANG parameter , you have to import with option indexes=N and then you should create your index after you remove duplicates.

            • 4. Re: Binary to character conversion problem
              Raluce

              Hi Thanks for your replay i used option INDEX=N and the index error is solved but still the NLS problem is not. I created new database instance in my server with the same CHARACTER SET WE8ISO8859P15 and import the table in it and there is no problem and my query using ROWTOHEX is giving the right result.

              My problem is really Character set but the thing i will do is changing my database charset to WE8ISO8859P1 to avoid the same problem.

              Thank you for your help.

               

              Regards,

               

              raluce

              • 5. Re: Binary to character conversion problem
                jgarry

                Fran's link had a slightly more appropriate set of entries starting here: Ask Tom Import and export between dbs with diff...

                • 6. Re: Binary to character conversion problem
                  mkeskin

                  Possible datacorruption with Impdp if importing NLS_CHARACTERSET is different from exporting NLS_CHARACTERSET (Doc ID 457526.1)

                   

                  The following table gives a overview of often used NLS_CHARACTERSET combinations:

                   

                   

                  Source NLS_CHARACTERSETTarget NLS_CHARACTERSETCorruption can happen?
                  NLS_CHARACTERSET is the same as targetNLS_CHARACTERSET is the same as sourceNo
                  US7ASCIIxx8ISO8859Px / xx8MSWIN12xxNo
                  US7ASCIIJA16xxxx / KO16xxxx / ZHS16xxxx / ZHT16xxxxNo
                  US7ASCIIAL32UTF8 / UTF8No
                  xx8ISO8859Pxxx8MSWIN12xxYes, probability is very low.  NOT possible for WE8ISO8859P1 to WE8MSWIN1252
                  xx8MSWIN12xxxx8ISO8859PxYes, probability is very low.
                  xx8ISO8859Px / xx8MSWIN12xxAL32UTF8 / UTF8Yes
                  JA16xxxx / KO16xxxx / ZHS16xxxx / ZHT16xxxxAL32UTF8 / UTF8Yes
                  AL32UTF8 / UTF8xx8ISO8859Px / xx8MSWIN12xx /JA16xxxx, KO16xxxx / ZHS16xxxx / ZHT16xxxxYes
                  AL32UTF8UTF8Normally NO, Yes if you have "convertible" in csscan - wich is very rare.
                  UTF8AL32UTF8Normally NO, Yes if you have "convertible" in csscan - which is very rare.

                   

                  Note that it is still possible to "lose" data by doing a incorrect conversion.

                  • 7. Re: Binary to character conversion problem
                    mkeskin

                    Read this support notes ,

                     

                    Problems with (Importing) Encrypted Data After Character Set Change Using Other NLS_CHARACTERSET Database or Upgrading the (client) Oracle Version (Doc ID 1297507.1)


                    AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1)

                     

                    B.12) Make sure you do not store "binary" (  pdf , doc, docx, jpeg, png , etc files) or Encrypted data (passwords) in character datatypes (CHAR, VARCHAR2, LONG, CLOB).

                    If binary data ( like PDF , doc, docx, jpeg, png , etc  files) or encrypted data ( like hashed/encrypted passwords ) is stored/handled as a CHAR, VARCHAR2, LONG or CLOB datatype than data loss is expected, especially when using an AL32UTF8 database (even without using exp/imp). Or errors like ORA-29275 or ORA-600 [kole_t2u], [34] may appear.

                    The ONLY supported data types to store "raw" binary data (like PDF , doc, docx, jpeg, png , etc  files) or encrypted data ( like hashed/encrypted passwords) are LONG RAW or BLOB.
                    If you want to store binary data (like PDF , doc, docx, jpeg, png , etc files) or encrypted data like hashed/encrypted passwords in CHAR, VARCHAR2, LONG or CLOB datatype than this must be converted to a "characterset safe" representation like base64 in the application layer.

                    Note 1297507.1 Problems with (Importing) Encrypted Data After Character Set Change Using Other NLS_CHARACTERSET Database or Upgrading the (client) Oracle Version
                    Note 1307346.1 DBMS_LOB Loading and Extracting Binary File To Oracle Database