ORA-06512:确切的fetch返回超过请求的行数开发者社区

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

咆哮的黄豆 · Pycharm ...· 9 月前 ·

很酷的橡皮擦 · MongoDB设置登录账号和密码 - ...· 1 年前 ·

正直的香瓜 · 如何根据最高z索引获取元素属性-腾讯云开发者 ...· 1 年前 ·

笑点低的西红柿 · TortoiseGit使用 - 风御之举 ...· 1 年前 ·

坚韧的开心果 · ModuleNotFoundError: ...· 2 年前 ·

腾讯云

开发者社区

文档意见反馈控制台

首页 TVP

文章/答案/技术大牛

发布

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

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)
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)
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);
  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 ;

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;

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

回答 3

Stack Overflow用户

Stack Overflow用户