问题描述

数据库版本从03134284336-20250427-271283-20132 Pack19 升级为 03134284404-20251210-306367-20164 Pack16后，有一个存储过程执行报错对象定义被修改，升级前执行一直正常。


存储过程定义如下

CREATE OR REPLACE PROCEDURE QD.P_QD_NP_BASE_INFO_D_TEST(V_ACCT_DAY IN VARCHAR,
V_RETCODE OUT VARCHAR,
V_RETINFO OUT VARCHAR) IS
V_OWNER VARCHAR(20); --用户
V_TABLENAME VARCHAR(200); --表名
V_PROCNAME VARCHAR(2000);
V_ROWLINE NUMBER;
V_NO NUMBER;
V_MONTH_ID VARCHAR(6);
V_DAY_ID VARCHAR(2);
V_MAX_DAY VARCHAR(8);
V_PART VARCHAR(50);
V_PART_NUM NUMBER;
V_ADDPART_SQL VARCHAR(500);
V_TRUNCATEPART_SQL VARCHAR(500);
V_TABLE_NAME VARCHAR(500);
BEGIN
V_OWNER := 'QD';
V_TABLENAME := 'QD_NP_BASE_INFO_D';
V_PROCNAME := 'P_QD_NP_BASE_INFO_D';
V_ROWLINE := 0;
V_MONTH_ID := SUBSTR(V_ACCT_DAY, 1, 6);
V_DAY_ID := SUBSTR(V_ACCT_DAY, 7, 2);
V_PART := 'PART' || V_ACCT_DAY;
V_PART_NUM := 0;
--日志部分
TASK.P_INSERT_LOG(V_ACCT_DAY, 
V_TABLENAME, 
V_PROCNAME, 
'xxx',
SYSDATE);
FG_APP.P_SEND_SMS('18526121356',
V_ACCT_DAY || ':' || V_PROCNAME ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' 开始执行');
INSERT INTO QD.QD_NP_BASE_INFO_B
SELECT USER_NO,
DEVICE_NUMBER,
PRODUCT_NAME,
INNET_DATE,
INNET_MONTH,
TOTAL_FEE,
IS_HYJH_DESC,
ACTIVE_TYPE,
HYJH_END_DATE,
IS_RH_DESC,
RH_TYPE,
CY_CNDX,
CY_BJL,
IF_SAME_ID_KD,
NUM_PHONE,
PRODUCT_ID,
KD_SPEED,
CUSTOMER_NO,
CUST_NAME,
STATE_NAME,
L1_DATA,
L1_DURATION,
LM_PRODUCT_NAME,
DYSL_PRODUCT_NAME,
IS_RH,
AREA_NO,
WG_ID,
WG_NAME
FROM (SELECT USER_NO,
DEVICE_NUMBER,
PRODUCT_NAME,
INNET_DATE,
INNET_MONTH,
TOTAL_FEE,
IS_HYJH_DESC,
ACTIVE_TYPE,
HYJH_END_DATE,
IS_RH_DESC,
RH_TYPE,
CY_CNDX,
CY_BJL,
IF_SAME_ID_KD,
NUM_PHONE,
PRODUCT_ID,
KD_SPEED,
CUSTOMER_NO,
CUST_NAME,
STATE_NAME,
L1_DATA,
L1_DURATION,
LM_PRODUCT_NAME,
DYSL_PRODUCT_NAME,
IS_RH,
AREA_NO,
WG_ID,
WG_NAME,
ROW_NUMBER() OVER(
PARTITION BY DEVICE_NUMBER
ORDER BY INNET_DATE DESC) SEQ
FROM QD.DM2_NP_BASE_INFO_B)
WHERE SEQ = 1;
COMMIT;
EXECUTE IMMEDIATE 'ALTER TABLE QD.QD_NP_BASE_INFO_A RENAME TO QD_NP_BASE_INFO_C';
COMMIT;
EXECUTE IMMEDIATE 'ALTER TABLE QD.QD_NP_BASE_INFO_B RENAME TO QD_NP_BASE_INFO_A';
COMMIT;
EXECUTE IMMEDIATE 'ALTER TABLE QD.QD_NP_BASE_INFO_C RENAME TO QD_NP_BASE_INFO_B';
COMMIT;
FG_APP.P_SEND_SMS('18526121356',
V_ACCT_DAY || ':' || V_PROCNAME ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' 全部结束');
--更新日志
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
TASK.P_UPDATE_LOG(V_ACCT_DAY,
V_TABLENAME,
V_PROCNAME,
V_RETINFO,
V_RETCODE,
V_ROWLINE,
SYSDATE);
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
TASK.P_UPDATE_LOG(V_ACCT_DAY,
V_TABLENAME,
V_PROCNAME,
V_RETINFO,
V_RETCODE,
V_ROWLINE,
SYSDATE);
END;

解决方案

将存储过程中的insert语句用EXECUTE IMMEDIATE ‘’ 改为动态执行后不再报错。