ORA-06512:确切的fetch返回超过请求的行数
ORA-06512:确切的fetch返回超过请求的行数
提问于 2016-11-17 07:28:45
在运行下面的过程时，我会得到以下错误
Error report:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "my_db.CREATE_CTAB_FILE", line 51
ORA-06512: at line 2
01422. 00000 - "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请您给我建议一下，如何将获取的数据赋值到变量中？我怀疑数据类型varchar2的变量(lnbuf)不接受select语句获取的行。请帮助我解决这个错误。
create or replace
PROCEDURE CREATE_CTAB_FILE (f_ctab_id IN T_WN_CTAB_str.wn_ctab_id%type,
P_SYN_DIR in varchar2,
p_user IN varchar2)
is
FILEHANDLER WN_DB_UTL_FILE.FILE_TYPE;
LNBUF varchar2(4000) := null;
--cast(lnbuf as char(4000));
--LNBUF clob;
v_file varchar2(256);
V_DATA_LOG varchar2(256);
V_WN_COUNTRY T_WN_CTAB_STR.WN_COUNTRY%type;
V_H_L varchar2(256);
BEGIN
for CTAB_REC in
(select distinct WN_COUNTRY, WN_PRHB_DATE
from t_wn_ctab_h
where wn_ctab_id =f_ctab_id)
loop
v_wn_country:=ctab_rec.wn_country;
--bkk_solution.wn_insert_res_log('create_ctab', NULL, NULL, help_rec.wn_cty, help_rec.wn_vers_sol, 'START', p_cty);
v_file:=v_wn_country||'_CTAB'||'.txt';
fileHandler := WN_DB_UTL_FILE.FOPEN(P_SYN_DIR,V_FILE,'W');
--p_file_id := fileHandler.id;
lnbuf := ('#' || rpad(' ',42,' ') ||'V3.4.5'||rpad(' ',197,' ') || '000000000000000000000000000'||to_char(sysdate,'YYYYMMDD')|| to_char(ctab_rec.WN_PRHB_DATE,'YYYYMMDD') ||rpad(' ',48,' ')|| '1'); -- '<html>' -Tag ausgeben
WN_DB_UTL_FILE.PUTF(fileHandler, lnbuf);
Loop
LNBUF := NULL ;
SELECT ( CAST ('LT01' AS CHAR (8))
|| CAST (REC.WN_COUNTRY AS CHAR (3))
|| CAST (rec.WN_NORMSSNR_WW AS CHAR (32))
|| CAST (REC.WN_SOFTWARE AS CHAR (7))
|| CAST (REC.WN_NORMSSNR AS CHAR (32))
|| RPAD (' ', 32, ' ')
|| CAST (REC.WN_NAME_WW AS CHAR (16))
|| CAST (REC.WN_NAME AS CHAR (16))
|| CAST (REC.WN_NORMBYTE AS CHAR (6))
|| CAST ('0' AS CHAR (3))
|| CAST ('ST' AS CHAR (2))
|| RPAD (' ', 70, ' ')
|| CAST ('ST' AS CHAR (3))
|| RPAD (' ', 60, ' ')
|| TO_CHAR (rec.WN_FOD, 'YYYYMMDD')
|| TO_CHAR (rec.WN_LOD, 'YYYYMMDD'))
INTO lnbuf
FROM t_wn_ctab_str rec
WHERE rec.wn_ctab_id = 1234;
WN_DB_UTL_FILE.PUTF(fileHandler, lnbuf);
end LOOP;
WN_DB_UTL_FILE.FCLOSE(fileHandler);
end LOOP;
END CREATE_CTAB_FILE ;回答 3
Stack Overflow用户
回答已采纳
发布于 2016-11-17 08:28:21
正如错误所建议的，并在下面解释您的select查询返回了超过1行，并且您试图将其存储在一个变量中，这将减少问题。按以下方式更改您的循环：
for rec in (
SELECT ( CAST ('LT01' AS CHAR (8))
|| CAST (REC.WN_COUNTRY AS CHAR (3))
|| CAST (rec.WN_NORMSSNR_WW AS CHAR (32))
|| CAST (REC.WN_SOFTWARE AS CHAR (7))
|| CAST (REC.WN_NORMSSNR AS CHAR (32))
|| RPAD (' ', 32, ' ')
|| CAST (REC.WN_NAME_WW AS CHAR (16))
|| CAST (REC.WN_NAME AS CHAR (16))
|| CAST (REC.WN_NORMBYTE AS CHAR (6))
|| CAST ('0' AS CHAR (3))
|| CAST ('ST' AS CHAR (2))
|| RPAD (' ', 70, ' ')
|| CAST ('ST' AS CHAR (3))
|| RPAD (' ', 60, ' ')
|| TO_CHAR (rec.WN_FOD, 'YYYYMMDD')
|| TO_CHAR (rec.WN_LOD, 'YYYYMMDD')) col1
FROM t_wn_ctab_str rec
WHERE rec.wn_ctab_id = 1234 )
loop
LNBUF := NULL ;
LNBUF := rec.col1;
WN_DB_UTL_FILE.PUTF(fileHandler, lnbuf);
end LOOP;Stack Overflow用户
发布于 2016-11-17 07:46:33
不能将多行提取到标量变量中；例如：
SQL> declare
2 vName varchar2(100);
3 begin
4 select first_name
5 into vName
6 from employees
7 where rownum < 3;
8 --
9 dbms_output.put_line(vName);
10 end;
11 /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4如果需要单个值，则必须确保查询只返回一行：
SQL> declare
2 vName varchar2(100);
3 begin
4 select first_name
5 into vName
6 from employees
7 where rownum = 1;
8 --
9 dbms_output.put_line(vName);
10 end;
11 /
Ellen如果需要获得多行，可以使用大容量收集和适当的变量：
SQL> declare
2 type tTabNames is table of varchar2(100);
3 vNames tTabNames ;
4 begin
5 select first_name
6 bulk collect into vNames
7 from employees
8 where rownum < 3;
9 --
10 for i in 1 .. vNames.count loop
11 dbms_output.put_line(vNames(i));
12 end loop;
13 end;
14 /
Ellen
SundarStack Overflow用户
发布于 2016-11-17 12:26:26
当Select语句返回多行时，select语句中会遇到错误。由于静态变量只能在其中存储一个值，所以我们需要做的是更改select语句的where子句以只获取一个记录，或者在for循环中使用查询。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40649015
复制相关文章
相似问题
腾讯云开发者
