由于字符集问题导致 Package Body created with compilation errors.

将要在生产库上进行包的预发布，时遇到如下情况：Warning: Package Body created with compilation errors.整个过程如下：

首先检查对象的有效个数和无效的个数。

IM@im1>select object_type,status,count(*) from user_objects group by object_type,status;

OBJECT_TYPE        STATUS    COUNT(*)

------------------ ------- ----------

TYPE               VALID           11

VIEW               INVALID          3

INDEX              VALID          399

TABLE              VALID          234

PACKAGE            VALID           49

SYNONYM            VALID           10

FUNCTION           VALID            1

SEQUENCE           VALID          173

PROCEDURE          VALID           22

PACKAGE BODY       VALID           49

DATABASE LINK      VALID            6

INDEX PARTITION    VALID           79

TABLE PARTITION    VALID           79

13 rows selected.

发布是遇到如下错误：

IM@im1>@/tmp/IM_SMS_20110727.pkg

Package created.

Warning: Package Body created with compilation errors.

show error 检查，无结果显示。考虑不是语法的问题。

IM@im1>show error

No errors.

检查对象的有效个数：

IM@im1>select object_type,status,count(*) from user_objects group by object_type,status;

OBJECT_TYPE        STATUS    COUNT(*)

------------------ ------- ----------

TYPE               VALID           11

VIEW               INVALID          3

INDEX              VALID          399

TABLE              VALID          234

PACKAGE            VALID           49

SYNONYM            VALID           10

FUNCTION           VALID            1

SEQUENCE           VALID          173

PROCEDURE          VALID           22

PACKAGE BODY       VALID           48

PACKAGE BODY       INVALID          1  --多一个无效对象

DATABASE LINK      VALID            6

INDEX PARTITION    VALID           79

TABLE PARTITION    VALID           79

14 rows selected.

测试库的字符集为NLS_CHARACTERSET= AL32UTF8，而编辑的脚本的windows平台是ANSI,导致对中文的不兼容。

解决办法是测试环境的NLS_LANG为"AMERICAN_AMERICA.US7ASCII"，重新执行包的发布。

IM@im1>select * from nls_database_parameters;

PARAMETER               VALUE

-------------          ------------

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_LANGUAGE           AMERICAN

NLS_TERRITORY          AMERICA

NLS_CURRENCY           $ 

NLS_ISO_CURRENCY       AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET       AL32UTF8

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_RDBMS_VERSION      9.2.0.8.0

20 rows selected.

IM@im1>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

oracle@c9e016r3ectk1xl67j8p:aliuid /tmp>export|grep LANG

declare -x LANG="en_US.UTF-8"

declare -x NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

oracle@c9e016r3ectk1xl67j8p:/tmp>

oracle@c9e016r3ectk1xl67j8p:/tmp>

oracle@c9e016r3ectk1xl67j8p: /tmp>export NLS_LANG="AMERICAN_AMERICA.US7ASCII"

oracle@c9e016r3ectk1xl67j8p: /tmp>

oracle@c9e016r3ectk1xl67j8p: /tmp>

oracle@c9e016r3ectk1xl67j8p: /tmp>export|grep LANG

declare -x LANG="en_US.UTF-8"

declare -x NLS_LANG="AMERICAN_AMERICA.US7ASCII"

oracle@c9e016r3ectk1xl67j8p: /tmp>

再次执行包的变更，程序正确执行。

oracle@c9e016r3ectk1xl67j8p: /tmp>sqlplus IM   

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Jul 29 04:01:29 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password: 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

IM@im1>@/tmp/IM_SMS_20110727.pkg

Package created.

Package body created.

IM@im1>select object_type,status,count(*) from user_objects group by object_type,status;

OBJECT_TYPE        STATUS    COUNT(*)

------------------ ------- ----------

TYPE               VALID           11

VIEW               INVALID          3

INDEX              VALID          399

TABLE              VALID          234

PACKAGE            VALID           49

SYNONYM            VALID           10

FUNCTION           VALID            1

SEQUENCE           VALID          173

PROCEDURE          VALID           22

PACKAGE BODY       VALID           49

DATABASE LINK      VALID            6

INDEX PARTITION    VALID           79

TABLE PARTITION    VALID           79

13 rows selected.

IM@im1>@/tmp/IM_WEB_ONLIE_20110727.pkg

Package created.

Package body created.

IM@im1>select object_type,status,count(*) from user_objects group by object_type,status;

OBJECT_TYPE        STATUS    COUNT(*)

------------------ ------- ----------

TYPE               VALID           11

VIEW               INVALID          3

INDEX              VALID          399

TABLE              VALID          234

PACKAGE            VALID           49

SYNONYM            VALID           10

FUNCTION           VALID            1

SEQUENCE           VALID          173

PROCEDURE          VALID           22

PACKAGE BODY       VALID           49

DATABASE LINK      VALID            6

INDEX PARTITION    VALID           79

TABLE PARTITION    VALID           79

13 rows selected.

IM@im1>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

问题解决。。