我要再JOB中定时执行一个存储过程。这个存储过程我手工执行没有任何问题
但是再JOB中执行就会报错
ORA-01422: exact fetch returns more than requested number of rows而且再下面这个地方报错
SZSQL := 'create table acct_item_list_temp1 tablespace YS_TEMP_DAT_1 as
SELECT a.IOID_ID0 SERV_ID,
a.ioid_rev,
A.AREA_CODE,
A.ACC_NBR,
a.payment_mode,
A.SERV_NAME,
C.DETAIL,
A.CUST_ID,
A.ACCT_ID,
B.CUST_LEVEL,
A.SERV_STATE,
B.CUST_TYPE_ID,
B.RESERVED1
FROM SERV_T A, CUST_T B,ADDRESS_T C, (SELECT ioid_id0,MAX(ioid_rev) ioid_rev FROM serv_t
GROUP BY ioid_id0) d
WHERE A.partition_id_region BETWEEN ' ||
ANPIDREGIONMIN || ' AND ' || ANPIDREGIONMAX ||
' AND A.CUST_ID = B.IOID_ID0(+) AND B.STATE = ''70A'' AND A.ADDRESS_ID=C.ADDRESS_ID(+)
AND a.ioid_id0=d.ioid_id0 AND a.ioid_rev=d.ioid_rev ';
EXECUTE IMMEDIATE SZSQL;
我根本就没有INTO 的语句啊。求助各位给帮帮忙 在线等
但是再JOB中执行就会报错
ORA-01422: exact fetch returns more than requested number of rows而且再下面这个地方报错
SZSQL := 'create table acct_item_list_temp1 tablespace YS_TEMP_DAT_1 as
SELECT a.IOID_ID0 SERV_ID,
a.ioid_rev,
A.AREA_CODE,
A.ACC_NBR,
a.payment_mode,
A.SERV_NAME,
C.DETAIL,
A.CUST_ID,
A.ACCT_ID,
B.CUST_LEVEL,
A.SERV_STATE,
B.CUST_TYPE_ID,
B.RESERVED1
FROM SERV_T A, CUST_T B,ADDRESS_T C, (SELECT ioid_id0,MAX(ioid_rev) ioid_rev FROM serv_t
GROUP BY ioid_id0) d
WHERE A.partition_id_region BETWEEN ' ||
ANPIDREGIONMIN || ' AND ' || ANPIDREGIONMAX ||
' AND A.CUST_ID = B.IOID_ID0(+) AND B.STATE = ''70A'' AND A.ADDRESS_ID=C.ADDRESS_ID(+)
AND a.ioid_id0=d.ioid_id0 AND a.ioid_rev=d.ioid_rev ';
EXECUTE IMMEDIATE SZSQL;
我根本就没有INTO 的语句啊。求助各位给帮帮忙 在线等
建议lz把你的动态sql改为等效的静态sql执行,看看返回多少行记录把
个人意见是:
AND A.CUST_ID = B.IOID_ID0(+) AND B.STATE = ''70A'' AND A.ADDRESS_ID=C.ADDRESS_ID(+)
值得推敲
以上这部分语句必须改成
AND A.CUST_ID = B.IOID_ID0(+) AND B.STATE(+) = ''70A'' AND A.ADDRESS_ID=C.ADDRESS_ID(+) 否则 A与B的连接就不是外连接而是内连接。能不能把 create table tn 改成 先在外边创建好tn ,之后试试insert into tn .