紧急求助 再JOB中执行存储过程会报ORA-01422: exact fetch returns more than requested number of rows错误

[wzj_1979]

我要再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 的语句啊。求助各位给帮帮忙 在线等

[wzj_1979]

但是用 EXECUTE DBMS_JOB.RUN();执行这个JOB 却不报错。直接手工执行存储过程也不报错。而且虽然是动态SQL 但是并没有用到游标啊

[tgm78]

ORA-01422 exact fetch returns more than requested number of rows

Cause: The number specified in exact fetch is less than the rows returned.

Action: Rewrite the query or change number of rows requested.

从错误提示信息看，你的sql写的有问题
个人意见是：
AND A.CUST_ID = B.IOID_ID0(+) AND B.STATE = ''70A'' AND A.ADDRESS_ID=C.ADDRESS_ID(+)
值得推敲

[wzj_1979]

那为什么手动启动JOB 就没有问题呢？

[tgm78]

ORA-01422: exact fetch returns more than requested number of rows

错误提示的很清楚了，游标返回了多行结果，需要查看你的sql语句了。
建议lz把你的动态sql改为等效的静态sql执行，看看返回多少行记录把

[wzj_1979]

期待回家啊

[hem]

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 .

[QingT26]

高手们能教下怎么手动启动JOB吗？我维护中正遇到JOB没有启动。