2 Replies Latest reply: Nov 26, 2012 3:45 PM by Ohselotl RSS

    Error while running package ORA-06553: PLS-553 character set name is not re

    Ohselotl
      Hi all.

      I have a problem with a package, when I run it returns me code error:

      ORA-06552: PL/SQL: Compilation unit analysis terminated
      ORA-06553: PLS-553: character set name is not recognized

      The full context of the problem is this:

      Previously I had a developing data base, then was migrated to a new server. After that I started to receive the error, so I began to check for the solution.

      My first move was compare the “old database” with the “new database”, so I check the nls parameters, and this was the result:

      select * from nls_database_parameters;

      Result from the old

      NLS_LANGUAGE     AMERICAN
      NLS_TERRITORY     AMERICA
      NLS_CURRENCY     $
      NLS_ISO_CURRENCY     AMERICA
      NLS_NUMERIC_CHARACTERS     .,
      NLS_CHARACTERSET     US7ASCII
      NLS_CALENDAR     GREGORIAN
      NLS_DATE_FORMAT     DD-MON-RR
      NLS_DATE_LANGUAGE     AMERICAN
      NLS_SORT     BINARY
      NLS_TIME_FORMAT     HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
      NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY     $
      NLS_COMP     BINARY
      NLS_LENGTH_SEMANTICS     BYTE
      NLS_NCHAR_CONV_EXCP     FALSE
      NLS_NCHAR_CHARACTERSET     AL16UTF16
      NLS_RDBMS_VERSION     10.2.0.1.0

      Result from the new

      NLS_LANGUAGE     AMERICAN
      NLS_TERRITORY     AMERICA
      NLS_CURRENCY     $
      NLS_ISO_CURRENCY     AMERICA
      NLS_NUMERIC_CHARACTERS     .,
      NLS_CHARACTERSET     US7ASCII
      NLS_CALENDAR     GREGORIAN
      NLS_DATE_FORMAT     DD-MON-RR
      NLS_DATE_LANGUAGE     AMERICAN
      NLS_SORT     BINARY
      NLS_TIME_FORMAT     HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
      NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY     $
      NLS_COMP     BINARY
      NLS_LENGTH_SEMANTICS     BYTE
      NLS_NCHAR_CONV_EXCP     FALSE
      NLS_NCHAR_CHARACTERSET     AL16UTF16
      NLS_RDBMS_VERSION     10.2.0.1.0

      As the result was identical, I decided to look for more info in the log file of the new database. What I find was this:

      *********************************************************************
      Database Characterset is US7ASCII
      Threshold validation cannot be done before catproc is loaded.
      Threshold validation cannot be done before catproc is loaded.
      *********************************************************************
      alter database character set INTERNAL_CONVERT WE8MSWIN1252
      Updating character set in controlfile to WE8MSWIN1252
      Synchronizing connection with database character set information
      Refreshing type attributes with new character set information
      Completed: alter database character set INTERNAL_CONVERT WE8MSWIN1252
      *********************************************************************
      alter database character set US7ASCII
      ORA-12712 signalled during: alter database character set US7ASCII...
      alter database character set US7ASCII
      ORA-12712 signalled during: alter database character set US7ASCII...
      Errors in file e:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3132.trc:

      Regards
        • 1. Re: Error while running package ORA-06553: PLS-553 character set name is not re
          Srini Chavali-Oracle
          Ohselotl wrote:
          Hi all.

          I have a problem with a package, when I run it returns me code error:

          ORA-06552: PL/SQL: Compilation unit analysis terminated
          ORA-06553: PLS-553: character set name is not recognized

          The full context of the problem is this:

          Previously I had a developing data base, then was migrated to a new server. After that I started to receive the error, so I began to check for the solution.

          My first move was compare the “old database” with the “new database”, so I check the nls parameters, and this was the result:

          select * from nls_database_parameters;

          Result from the old

          NLS_LANGUAGE     AMERICAN
          NLS_TERRITORY     AMERICA
          NLS_CURRENCY     $
          NLS_ISO_CURRENCY     AMERICA
          NLS_NUMERIC_CHARACTERS     .,
          NLS_CHARACTERSET     US7ASCII
          NLS_CALENDAR     GREGORIAN
          NLS_DATE_FORMAT     DD-MON-RR
          NLS_DATE_LANGUAGE     AMERICAN
          NLS_SORT     BINARY
          NLS_TIME_FORMAT     HH.MI.SSXFF AM
          NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
          NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
          NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
          NLS_DUAL_CURRENCY     $
          NLS_COMP     BINARY
          NLS_LENGTH_SEMANTICS     BYTE
          NLS_NCHAR_CONV_EXCP     FALSE
          NLS_NCHAR_CHARACTERSET     AL16UTF16
          NLS_RDBMS_VERSION     10.2.0.1.0

          Result from the new

          NLS_LANGUAGE     AMERICAN
          NLS_TERRITORY     AMERICA
          NLS_CURRENCY     $
          NLS_ISO_CURRENCY     AMERICA
          NLS_NUMERIC_CHARACTERS     .,
          NLS_CHARACTERSET     US7ASCII
          NLS_CALENDAR     GREGORIAN
          NLS_DATE_FORMAT     DD-MON-RR
          NLS_DATE_LANGUAGE     AMERICAN
          NLS_SORT     BINARY
          NLS_TIME_FORMAT     HH.MI.SSXFF AM
          NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
          NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
          NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
          NLS_DUAL_CURRENCY     $
          NLS_COMP     BINARY
          NLS_LENGTH_SEMANTICS     BYTE
          NLS_NCHAR_CONV_EXCP     FALSE
          NLS_NCHAR_CHARACTERSET     AL16UTF16
          NLS_RDBMS_VERSION     10.2.0.1.0

          As the result was identical, I decided to look for more info in the log file of the new database. What I find was this:

          *********************************************************************
          Database Characterset is US7ASCII
          Threshold validation cannot be done before catproc is loaded.
          Threshold validation cannot be done before catproc is loaded.
          *********************************************************************
          alter database character set INTERNAL_CONVERT WE8MSWIN1252
          Updating character set in controlfile to WE8MSWIN1252
          Synchronizing connection with database character set information
          Refreshing type attributes with new character set information
          Completed: alter database character set INTERNAL_CONVERT WE8MSWIN1252
          *********************************************************************
          This is an unsupported method to change the characterset of a database - it has caused the corruption of your database beyond repair. Hopefully you have a backup you can recover from. Whoever did this did not know what they were doing.
          alter database character set US7ASCII
          ORA-12712 signalled during: alter database character set US7ASCII...
          alter database character set US7ASCII
          ORA-12712 signalled during: alter database character set US7ASCII...
          Errors in file e:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3132.trc:

          Regards
          The correct way to change the characterset of a database is documented - http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch11charsetmig.htm#sthref1476

          HTH
          Srini
          • 2. Re: Error while running package ORA-06553: PLS-553 character set name is not re
            Ohselotl
            Thank you very much for your quick response