Oracle:10.0.1.2.0的bug!

处理对象类型 SCHEMA_EXPORT/FUNCTION/FUNCTION
ORA-39083: 对象类型 FUNCTION 创建失败, 出现错误:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在 "SYS.KUPW$WORKER", line 11729
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
失败的 sql 为:
CREATE FUNCTION "STUDY"."FUN_SALERSTRUTEST" ( --用途：促销员结构分析查询
VDEPT NUMBER, ----事业部
VTOP NUMBER, ----顶点机构
VSDATE VARCHAR2, ----开始日期
VEDATE VARCHAR2, ----结束日期
VSALERSTATE NUMBER, ----促销员状态：所有：－1，在职：1，离职：0 AS
VORG2 NUMBER, ----大区 1或0 表选与
ORA-39083: 对象类型 FUNCTION 创建失败, 出现错误:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在 "SYS.KUPW$WORKER", line 11729
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
失败的 sql 为:
CREATE FUNCTION "STUDY"."FUN_TEST" (
VDEPT NUMBER, ----事业部
VTOP NUMBER, ----顶点机构
VPRODUCTTYPE NUMBER, ----产品类型
VSDAT VARCHAR2, ----开始日期
VEDAT VARCHAR2) ----结束日期
RETURN LONG
AS
VBDATE DATE := TO_DATE(VSDAT,'YYYY-MM-DD'); ----开始日期
VEDATE DATE :=
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
作业 "SYS"."SYS_IMPORT_SCHEMA_01" 因致命错误于 16:10 停止

--------------------next---------------------
. . 导出了 "STUDY"."T_TRACKORGPLAN" 0 KB 0 行
. . 导出了 "STUDY"."T_TRACKSALEREGIONPLAN" 0 KB 0 行
. . 导出了 "STUDY"."USERDATA" 0 KB 0 行
. . 导出了 "STUDY"."USERDEPARTMENT" 0 KB 0 行
. . 导出了 "STUDY"."USERGROUP" 0 KB 0 行
. . 导出了 "STUDY"."USERORGRIGHT" 0 KB 0 行
. . 导出了 "STUDY"."USERPRODUCTRIGHT" 0 KB 0 行
. . 导出了 "STUDY"."YEARPLAN" 0 KB 0 行
. . 导出了 "STUDY"."YEARPLANPRODUCT" 0 KB 0 行
已成功加载/卸载了主表 "STUDY"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
STUDY.SYS_EXPORT_SCHEMA_01 的转储文件集为:
H:\TEMP\EXPDP\EXPDP_STUDY_20050816.DMP
作业 "STUDY"."SYS_EXPORT_SCHEMA_01" 已于 10:20 成功完成


F:\ORACLE\bin>impdp dumpfile=expdp_study_20050816.dmp logfile=impdp_study_20050816.log schemas=stu
dy userid=study/1

Import: Release 10.1.0.2.0 - Production on 星期二, 16 8月, 2005 10:25

Copyright (c) 2003, . All rights reserved.

连接到: Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "STUDY"."SYS_IMPORT_SCHEMA_01"
启动 "STUDY"."SYS_IMPORT_SCHEMA_01": dumpfile=expdp_study_20050816.dmp logfile=impdp_study_200508
16.log schemas=study userid=study/********
处理对象类型 SCHEMA_EXPORT/USER
ORA-31684: 对象类型 USER:"STUDY" 已存在
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/TABLESPACE_QUOTA
处理对象类型 SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . 导入了 "STUDY"."ABCTYPE" 0 KB 0 行
. . 导入了 "STUDY"."BAND" 0 KB 0 行
. . 导入了 "STUDY"."BIGCUST" 0 KB 0 行
......
. . 导入了 "STUDY"."USERPRODUCTRIGHT" 0 KB 0 行
. . 导入了 "STUDY"."YEARPLAN" 0 KB 0 行
. . 导入了 "STUDY"."YEARPLANPRODUCT" 0 KB 0 行
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
处理对象类型 SCHEMA_EXPORT/FUNCTION/FUNCTION
ORA-39083: 对象类型 FUNCTION 创建失败, 出现错误:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在 "SYS.KUPW$WORKER", line 11729
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
失败的 sql 为:
CREATE FUNCTION "STUDY"."FUN_SALERSTRUTEST" ( --用途：促销员结构分析查询
VDEPT NUMBER, ----事业部
VTOP NUMBER, ----顶点机构
VSDATE VARCHAR2, ----开始日期
VEDATE VARCHAR2, ----结束日期
VSALERSTATE NUMBER, ----促销员状态：所有：－1，在职：1，离职：0 AS
VORG2 NUMBER, ----大区 1或0 表选与
ORA-39083: 对象类型 FUNCTION 创建失败, 出现错误:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在 "SYS.KUPW$WORKER", line 11729
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
失败的 sql 为:
CREATE FUNCTION "STUDY"."FUN_TEST" (
VDEPT NUMBER, ----事业部
VTOP NUMBER, ----顶点机构
VPRODUCTTYPE NUMBER, ----产品类型
VSDAT VARCHAR2, ----开始日期
VEDAT VARCHAR2) ----结束日期
RETURN LONG
AS
VBDATE DATE := TO_DATE(VSDAT,'YYYY-MM-DD'); ----开始日期
VEDATE DATE :=
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

作业 "STUDY"."SYS_IMPORT_SCHEMA_01" 因致命错误于 10:26 停止


F:\ORACLE\bin>




--------------------next---------------------
24-AUG-05 05:55:14 GMT

.
ISSUE CLARIFICATION
====================
ORA-39083: Object type FUNCTION failed to create with error:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.KUPW$WORKER", line 11849
ORA-06502: PL/SQL: numeric or value error: character string buffer too small


eos (end of section)

.
ISSUE VERIFICATION
===================

Verified the issue through TAR update

eos (end of section)

.
DATA COLLECTED
===============
ORA-39083: Object type FUNCTION failed to create with error:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.KUPW$WORKER", line 11849
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Failing sql is:
CREATE FUNCTION "STUDY"."FUN_TEST" (
VDEPT NUMBER,
VTOP NUMBER,
VPRODUCTTYPE NUMBER,
VSDAT VARCHAR2,
VEDAT VARCHAR2)
RETURN LONG
AS
VBDATE DATE := TO_DATE(VSDAT,'
Processing object type
SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39083: Object type ALTER_FUNCTION failed to create with error:
ORA-04043: object FUN_SALERSTRUTEST does not exist
Failing sql is:
ALTER FUNCTION "STUDY"."FUN_SALERSTRUTEST" COMPILE PLSQL_OPTIMIZE_LEVEL=
2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE REUSE SETTINGS
TIMESTAMP '2005-08-16:14:05:40'
ORA-39083: Object type ALTER_FUNCTION failed to create with error:
ORA-04043: object FUN_TEST does not exist
Failing sql is:
ALTER FUNCTION "STUDY"."FUN_TEST" COMPILE PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE REUSE SETTINGS TIMESTAMP
'2005-08-16:14:05:43'



eos (end of section)

.
CAUSE DETERMINATION
====================
The import datapump fails to import the procedure when the Create procedure
statement includes multi-byte characters.

eos (end of section)


CAUSE JUSTIFICATION
====================
Bug 4108939 - IMPORT PROCEDURE WITH DATA PUMP GET ORA-6502, fixed in 10.2 and
10.1.0.5

eos (end of section)

.
POTENTIAL SOLUTION(S)
======================
install the 10.1.0.5 patchset when available (perhaps during the next autumn)
or
create manually these packages after the import

eos (end of section)


POTENTIAL SOLUTION JUSTIFICATION(S)
====================================
The patchset contains the fix of the bug

eos (end of section)

.
SOLUTION / ACTION PLAN
=======================

Create the function manually after the import

eos (end of section)

--------------------next---------------------