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
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 )
LNBUF := NULL ;
LNBUF := rec.col1;
WN_DB_UTL_FILE.PUTF(fileHandler, lnbuf);
end LOOP;
票数 0
EN
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