这是存储过程的部分代码;
V_SQL := 'CREATE GLOBAL TEMPORARY TABLE myTempTable
(
SERIAL_NO VARCHAR2(18) not null,
ACT_ID VARCHAR2(18) not null,
REGION_CODE VARCHAR2(5) not null,
GROUP_ID VARCHAR2(14) not null,
CHN_CODE VARCHAR2(5) not null,
ID_NO VARCHAR2(20) not null,
CUST_ID VARCHAR2(20) not null,
PHONE_NO VARCHAR2(14) not null,
MEANS_ID VARCHAR2(18) not null,
FEE_SUM NUMBER(12,2),
CASH_MONEY NUMBER(12,2),
ADD_PHONE_NO VARCHAR2(14),
SYS_PAY_TYPE VARCHAR2(50),
SYS_PAY_MONEY NUMBER(12,2),
PAY_TYPE_VALUE VARCHAR2(50),
GIFT_CODE VARCHAR2(100),
GIFT_NAME VARCHAR2(500),
CARD_TYPE VARCHAR2(100),
CARD_NUM VARCHAR2(100),
RESOURCE_BRAND VARCHAR2(50),
RESOURCE_MODEL VARCHAR2(50),
RESOURCE_COST_SUB NUMBER(12,2),
RESOURCE_COST_PRICE NUMBER(12,2),
RESOURCE_FEE NUMBER(12,2),
PRI_FEE_CODE VARCHAR2(50),
PRI_FEE_NAME VARCHAR2(100),
DISTRI_FEE_CODE VARCHAR2(50),
DISTRI_FEE_NAME VARCHAR2(100),
ADD_FEE_CODE VARCHAR2(500),
ADD_FEE_NAME VARCHAR2(1000),
SP_CODE VARCHAR2(500),
SP_NAME VARCHAR2(1000),
SCORE VARCHAR2(50),
CONSUME_SCORE VARCHAR2(50),
CONSUME_VALUE NUMBER(12,2),
DATA_CARD_NO VARCHAR2(50),
OPER_ID VARCHAR2(20),
OPER_NAME VARCHAR2(20),
OPER_TIME DATE,
BAK1 VARCHAR2(255),
BAK2 VARCHAR2(255),
BAK3 VARCHAR2(255),
OPER_TYPE VARCHAR2(50),
ORDER_ID VARCHAR2(18),
CASH_START_TIME DATE,
SYS_PAY_MONTH VARCHAR2(50),
SYS_PAY_PER NUMBER(12,2),
SYS_START_TIME DATE,
SYS_END_TIME DATE,
SYS_LIMIT_MONEY NUMBER(12,2),
IS_AWARD VARCHAR2(5),
GIFT_MODEL VARCHAR2(5),
CARD_IS_IRC VARCHAR2(5),
CARD_BEGIN_NO VARCHAR2(50),
CARD_END_NO VARCHAR2(50),
RESOURCE_IMEI_CODE VARCHAR2(50),
DELIVERY_TIME DATE,
QUALITY_LIMIT VARCHAR2(10),
BIZ_CODE VARCHAR2(200),
KX_CODE_BUNCH VARCHAR2(500),
PRI_FEE_VALID VARCHAR2(5)
)
ON COMMIT PRESERVE ROWS';
execute immediate V_SQL;
这是想建一个临时表,但是在测试的时候,在这行V_SQL := 'CREATE GLOBAL TEMPORARY TABLE myTempTable
报ora-06502 numeric or value error:character string buffer to small
请问要如何解决,我用的是PL/SQL DEVELOPER,谢谢了!
V_SQL := 'CREATE GLOBAL TEMPORARY TABLE myTempTable
(
SERIAL_NO VARCHAR2(18) not null,
ACT_ID VARCHAR2(18) not null,
REGION_CODE VARCHAR2(5) not null,
GROUP_ID VARCHAR2(14) not null,
CHN_CODE VARCHAR2(5) not null,
ID_NO VARCHAR2(20) not null,
CUST_ID VARCHAR2(20) not null,
PHONE_NO VARCHAR2(14) not null,
MEANS_ID VARCHAR2(18) not null,
FEE_SUM NUMBER(12,2),
CASH_MONEY NUMBER(12,2),
ADD_PHONE_NO VARCHAR2(14),
SYS_PAY_TYPE VARCHAR2(50),
SYS_PAY_MONEY NUMBER(12,2),
PAY_TYPE_VALUE VARCHAR2(50),
GIFT_CODE VARCHAR2(100),
GIFT_NAME VARCHAR2(500),
CARD_TYPE VARCHAR2(100),
CARD_NUM VARCHAR2(100),
RESOURCE_BRAND VARCHAR2(50),
RESOURCE_MODEL VARCHAR2(50),
RESOURCE_COST_SUB NUMBER(12,2),
RESOURCE_COST_PRICE NUMBER(12,2),
RESOURCE_FEE NUMBER(12,2),
PRI_FEE_CODE VARCHAR2(50),
PRI_FEE_NAME VARCHAR2(100),
DISTRI_FEE_CODE VARCHAR2(50),
DISTRI_FEE_NAME VARCHAR2(100),
ADD_FEE_CODE VARCHAR2(500),
ADD_FEE_NAME VARCHAR2(1000),
SP_CODE VARCHAR2(500),
SP_NAME VARCHAR2(1000),
SCORE VARCHAR2(50),
CONSUME_SCORE VARCHAR2(50),
CONSUME_VALUE NUMBER(12,2),
DATA_CARD_NO VARCHAR2(50),
OPER_ID VARCHAR2(20),
OPER_NAME VARCHAR2(20),
OPER_TIME DATE,
BAK1 VARCHAR2(255),
BAK2 VARCHAR2(255),
BAK3 VARCHAR2(255),
OPER_TYPE VARCHAR2(50),
ORDER_ID VARCHAR2(18),
CASH_START_TIME DATE,
SYS_PAY_MONTH VARCHAR2(50),
SYS_PAY_PER NUMBER(12,2),
SYS_START_TIME DATE,
SYS_END_TIME DATE,
SYS_LIMIT_MONEY NUMBER(12,2),
IS_AWARD VARCHAR2(5),
GIFT_MODEL VARCHAR2(5),
CARD_IS_IRC VARCHAR2(5),
CARD_BEGIN_NO VARCHAR2(50),
CARD_END_NO VARCHAR2(50),
RESOURCE_IMEI_CODE VARCHAR2(50),
DELIVERY_TIME DATE,
QUALITY_LIMIT VARCHAR2(10),
BIZ_CODE VARCHAR2(200),
KX_CODE_BUNCH VARCHAR2(500),
PRI_FEE_VALID VARCHAR2(5)
)
ON COMMIT PRESERVE ROWS';
execute immediate V_SQL;
这是想建一个临时表,但是在测试的时候,在这行V_SQL := 'CREATE GLOBAL TEMPORARY TABLE myTempTable
报ora-06502 numeric or value error:character string buffer to small
请问要如何解决,我用的是PL/SQL DEVELOPER,谢谢了!
插入表的数据精度大于表字段定义精度导致
--关键是你的V_SQL定义了是多长:
--V_SQL varchar2(4000)--如果这个变量定义的长度不够的话,会出现ORA-06502错误