oracle 转化数字异常,PL/SQL: 数字或值错误 : 字符到数值的转换错误

我就是我 2023-01-18 09:09 20阅读 0赞

执行存储报错，ORA-06512: 在 "FYMES.P\_BI\_ITEM\_SNYSAP", line 80

存储过程如下：  打了颜色的 是报错行

CREATE OR REPLACE PROCEDURE P\_BI\_ITEM\_SNYSAP(IN\_SHOP\_CODE VARCHAR2

, IN\_ITEM\_CODE IN VARCHAR2

, IN\_CREATBY   IN VARCHAR2

, IN\_PLANT\_CODE IN VARCHAR2

/\*

, IN\_ITEM\_TYPE IN VARCHAR2

, IN\_TTEM\_NAME IN VARCHAR2

, IN\_FROMDATE  IN VARCHAR2

, IN\_TODATE    IN VARCHAR2

\*/

) is

L\_PLANT\_COUNT NUMBER;

L\_PROD\_TYPE\_COUNT NUMBER;

L\_ITEM\_TYPE\_COUNT NUMBER;

L\_PLANT\_CODE VARCHAR2(20);

L\_ITEM\_TYPE\_CODE VARCHAR2(20);

L\_PROD\_TYPE\_CODE VARCHAR2(20);

L\_PROD\_TYPE\_NAME VARCHAR2(255);

L\_ITEM\_CODE VARCHAR2(20);

L\_ITEM\_COUNT NUMBER;

L\_IN\_ITEN\_CODE VARCHAR2(20);

BEGIN

\--  IF IN\_ITEM\_CODE IS NOT NULL AND TRIM(IN\_ITEM\_CODE) IS NOT NULL THEN

BEGIN

L\_ITEM\_COUNT:=0;

SELECT GE\_SAP\_ADD\_ZERO(UPPER(IN\_ITEM\_CODE),18) INTO L\_IN\_ITEN\_CODE FROM DUAL;

SELECT COUNT(1) INTO L\_ITEM\_COUNT FROM V\_SAP\_ITEM

WHERE MATNR =L\_IN\_ITEN\_CODE AND WERKS=IN\_PLANT\_CODE;

EXCEPTION WHEN NO\_DATA\_FOUND THEN

Raise\_Application\_error(-20050,'对不起！没找到符合条件的物料信息!');

END;

L\_PLANT\_COUNT:=0;

SELECT Werks,--工厂

MTART,--物料类型

Prodh,--产品类型

ITEM\_CODE --物料编码

INTO L\_PLANT\_CODE,L\_ITEM\_TYPE\_CODE, L\_PROD\_TYPE\_CODE,L\_ITEM\_CODE FROM  V\_SAP\_ITEM

WHERE MATNR =L\_IN\_ITEN\_CODE AND WERKS=IN\_PLANT\_CODE ;

SELECT COUNT(1) INTO L\_PLANT\_COUNT FROM BI\_PLANT  WHERE SHOP\_CODE =IN\_SHOP\_CODE

AND PLANT\_CODE=L\_PLANT\_CODE;

IF  L\_PLANT\_COUNT=0 THEN--判断工厂在MES系统中是否 存在，不存在 则新增

BEGIN

INSERT INTO BI\_PLANT (SHOP\_CODE,PLANT\_CODE,CREATE\_BY,CREATE\_DATE)

VALUES(IN\_SHOP\_CODE,L\_PLANT\_CODE,IN\_CREATBY,SYSDATE);

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20003,'INSERT INTO BI\_PLANT,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

END IF;

L\_ITEM\_TYPE\_COUNT:=0;

SELECT COUNT(1) INTO L\_ITEM\_TYPE\_COUNT  FROM BI\_ITEM\_TYPE WHERE SHOP\_CODE= IN\_SHOP\_CODE

AND ITEM\_TYPE\_CODE= L\_ITEM\_TYPE\_CODE;

IF  L\_ITEM\_TYPE\_COUNT= 0 THEN --判断物料类型在MES中是否存在，不存在 则新增

BEGIN

INSERT INTO BI\_ITEM\_TYPE (SHOP\_CODE,ITEM\_TYPE\_CODE,SYSTEM\_TYPE,CREATE\_BY,CREATE\_DATE)

VALUES(IN\_SHOP\_CODE,L\_ITEM\_TYPE\_CODE,'SAP',IN\_CREATBY,SYSDATE);

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20003,'INSERT INTO BI\_ITEM\_TYPE,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

END IF;

IF L\_PROD\_TYPE\_CODE IS  NOT NULL AND TRIM(L\_PROD\_TYPE\_CODE) IS NOT NULL THEN

BEGIN

L\_PROD\_TYPE\_COUNT:=0;

SELECT COUNT(1) INTO L\_PROD\_TYPE\_COUNT FROM BI\_PROD\_TYPE WHERE SHOP\_CODE = IN\_SHOP\_CODE

AND PROD\_TYPE\_CODE = L\_PROD\_TYPE\_CODE;

IF L\_PROD\_TYPE\_COUNT = 0 THEN --产品类型 若MES不存在，则新增一条产品类型，存在则根据主键进行产品类别名称更新

BEGIN

INSERT INTO BI\_PROD\_TYPE (SHOP\_CODE,PROD\_TYPE\_CODE,PROD\_TYPE\_NAME,SYSTEM\_TYPE,CREATE\_BY,CREATE\_DATE)

VALUES(IN\_SHOP\_CODE,L\_PROD\_TYPE\_CODE,L\_PROD\_TYPE\_NAME,'SAP',IN\_CREATBY,SYSDATE);

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20003,'INSERT INTO BI\_PROD\_TYPE,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

ELSE

BEGIN

UPDATE BI\_PROD\_TYPE SET PROD\_TYPE\_NAME=L\_PROD\_TYPE\_NAME,UPDATE\_BY=IN\_CREATBY,UPDATE\_DATE=SYSDATE

WHERE SHOP\_CODE=IN\_SHOP\_CODE AND  PROD\_TYPE\_CODE =L\_PROD\_TYPE\_CODE;

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20005,'UPDATE BI\_PROD\_TYPE,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

END IF;

END;

END IF;

L\_ITEM\_COUNT:=0;

SELECT COUNT(1) INTO L\_ITEM\_COUNT FROM BI\_ITEM WHERE SHOP\_CODE= IN\_SHOP\_CODE AND  ITEM\_CODE = L\_ITEM\_CODE;

IF L\_ITEM\_COUNT= 0 THEN --物料 若MES不存在，则新增一条物料信息，存在则根据主键进行物料更新

BEGIN

INSERT INTO BI\_ITEM (SHOP\_CODE,ITEM\_CODE,--ITEM\_NAME,UNIT\_CODE,ITEM\_TYPE\_CODE,ERNAM, ERSDA,AENAM,LAEDA,MATNR,

NEW\_FLAG,CREATE\_BY,CREATE\_DATE,SYSTEM\_TYPE)

VALUES(IN\_SHOP\_CODE,L\_ITEM\_CODE,1,IN\_CREATBY,SYSDATE,'SAP');

UPDATE BI\_ITEM  SET (ITEM\_NAME,UNIT\_CODE,ERNAM ,ERSDA,AENAM,LAEDA,MATNR,ITEM\_TYPE\_CODE,ITEM\_DESC)

= ( SELECT Normt,MEINS,ERNAM ,ERSDA,AENAM,LAEDA,MATNR, MTART,MAKTX

FROM V\_SAP\_ITEM  WHERE MATNR =L\_IN\_ITEN\_CODE AND WERKS=IN\_PLANT\_CODE)

WHERE BI\_ITEM.ITEM\_CODE=L\_ITEM\_CODE;

UPDATE BI\_ITEM  SET UPDATE\_BY=IN\_CREATBY,UPDATE\_DATE=SYSDATE

WHERE BI\_ITEM.ITEM\_CODE=L\_ITEM\_CODE;

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20005,'UPDATE BI\_ITEM,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

ELSE

BEGIN

UPDATE BI\_ITEM SET (ITEM\_NAME,UNIT\_CODE,ERNAM ,ERSDA,AENAM,LAEDA,MATNR,ITEM\_TYPE\_CODE,ITEM\_DESC)

= ( SELECT Normt,MEINS,ERNAM ,ERSDA,AENAM,LAEDA,MATNR ,MTART,MAKTX

FROM V\_SAP\_ITEM  WHERE MATNR =L\_IN\_ITEN\_CODE AND WERKS=IN\_PLANT\_CODE AND ITEM\_CODE=L\_ITEM\_CODE)

,UPDATE\_BY= IN\_CREATBY,UPDATE\_DATE=SYSDATE

WHERE BI\_ITEM.ITEM\_CODE=L\_ITEM\_CODE;

/\*       UPDATE BI\_ITEM SET UPDATE\_BY= IN\_CREATBY,UPDATE\_DATE=SYSDATE, ITEM\_NAME=V.Normt,UNIT\_CODE=V.MEINS,

ERNAM=V.ERNAM,ERSDA=V.ERSDA,AENAM=V.AENAM,LAEDA=V.LAEDA,MATNR=V.MATNR,

ITEM\_TYPE\_CODE=V.MTART,ITEM\_DESC=V.MAKTX

FROM BI\_ITEM B INNER JOIN  V\_SAP\_ITEM V

ON  (V.ITEM\_CODE=B.ITEM\_CODE AND  V.MATNR=L\_IN\_ITEN\_CODE AND V.Werks=IN\_PLANT\_CODE )

WHERE B.ITEM\_CODE=L\_ITEM\_CODE;\*/

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20005,'UPDATE BI\_ITEM,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

END IF;

\--判断是物料是否是成品 为产品  'ZFRT','FERT','ZFTI','ZFTO','HALB','ZHLB' 其他为物料

IF L\_ITEM\_TYPE\_CODE = 'HALB' OR L\_ITEM\_TYPE\_CODE = 'ZHLB' OR L\_ITEM\_TYPE\_CODE = 'ZFRT' OR L\_ITEM\_TYPE\_CODE = 'FERT' OR L\_ITEM\_CODE= 'ZFTI' OR L\_ITEM\_TYPE\_CODE = 'ZFTO'  THEN

BEGIN

L\_ITEM\_COUNT:=0;

SELECT COUNT(1) INTO L\_ITEM\_COUNT FROM BI\_PROD\_PLANT WHERE SHOP\_CODE = IN\_SHOP\_CODE AND ITEM\_CODE = L\_ITEM\_CODE;

IF L\_ITEM\_COUNT= 0 THEN

BEGIN

INSERT INTO BI\_PROD\_PLANT (SHOP\_CODE,PLANT\_CODE,PROD\_TYPE\_CODE,ITEM\_CODE,STATUS,CREATE\_BY,CREATE\_DATE)

VALUES(IN\_SHOP\_CODE,L\_PLANT\_CODE,L\_PROD\_TYPE\_CODE,L\_ITEM\_CODE,0,IN\_CREATBY,SYSDATE);

EXCEPTION WHEN OTHERS THEN

RAISE\_APPLICATION\_ERROR(-20005,'INSERT BI\_PROD\_PLANT,ora-'||TO\_CHAR(SQLERRM,1,255));

END;

END IF;

END;

END IF;

\--   END IF;

/\*    COMMIT;

EXCEPTION WHEN OTHERS THEN

ROLLBACK; \*/

END;

oracle 转化数字异常,PL/SQL: 数字或值错误 : 字符到数值的转换错误

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 js 字符串转换成数字两种情况（字符串为纯数字字符、数字字符加非数字字符）

相关字符与数字转换C++

相关 oracle 转化数字异常,PL/SQL: 数字或值错误 : 字符到数值的转换错误

相关 Java将字符串中的中文数字转化成阿拉伯数字或阿拉伯数字转化成中文数字

相关数字字符串和数字值（数值）转换函数

相关 Java中字符与数字的转换

相关 ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小错误分析

相关字符型数字与整形数字转换

相关 [转载]python 字符数字转换

相关 mongdb 数字转换异常