将要在生产库上进行包的预发布,时遇到如下情况: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
问题解决。。