错误信息:
ORA-01422: 实际返回的行数超出请求的行数
解析:
将返回值为结果集参数定义为oracle游标类型,本例使用SYS_REFCURSOR类型
1.报错存储过程
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS(ERRORMSG OUT VARCHAR2,
FAILINFO OUT VARCHAR2,
TABLENAME OUT VARCHAR2) IS
BEGIN
ERRORMSG := '';
SELECT FAIL_INFO, TABLE_NAME INTO FAILINFO,TABLENAME FROM F_LOG_INFO;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRORMSG := 'PRO_QUERY_INFO_ARG抛出异常: ' || SQLERRM;
END PRO_QUERY_INFO_ARGS;
2.变更后存储过程
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS2(ERRORMSG OUT VARCHAR2,
CURINFO OUT SYS_REFCURSOR) IS
BEGIN
ERRORMSG := '';
OPEN CURINFO FOR
SELECT FAIL_INFO, TABLE_NAME FROM F_LOG_INFO;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRORMSG := 'PRO_QUERY_INFO_ARG2抛出异常: ' || SQLERRM;
END PRO_QUERY_INFO_ARGS2;
3.本例中使用的建表语句
create table F_LOG_INFO
(
task_id NUMBER(16) not null,
begin_time DATE,
end_time DATE,
flag VARCHAR2(8),
fail_info VARCHAR2(512),
data_count NUMBER(16),
table_name VARCHAR2(256)
)
;
-- Add comments to the table
comment on table F_LOG_INFO
is '任务日志信息';
-- Add comments to the columns
comment on column F_LOG_INFO.task_id
is '任务标识';
comment on column F_LOG_INFO.begin_time
is '记录任务开始时间';
comment on column F_LOG_INFO.end_time
is '记录任务结束时间';
comment on column F_LOG_INFO.flag
is '执行标志 0:失败、1:成功';
comment on column F_LOG_INFO.fail_info
is '记录任务失败信息';
comment on column F_LOG_INFO.data_count
is '记录数据条数';
comment on column F_LOG_INFO.table_name
is '本次任务操作表名';
-- Create/Recreate primary, unique and foreign key constraints
alter table F_LOG_INFO
add constraint PK_F_LOG_INFO primary key (TASK_ID);
以上,感谢.