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

相关文章推荐

没读研的菠萝 · c++ - Is gettimeofday ...· 1 月前 ·

愉快的黄豆 · SQLServer中char、varchar ...· 1 月前 ·

飘逸的蘑菇 · 浅谈SQL ...· 1 月前 ·

威武的钥匙扣 · 大量插入（Transact-SQL） - ...· 1 周前 ·

爱健身的稀饭 · 什么是视频转码_对象存储(OSS)-阿里云帮助中心· 1 年前 ·

曾深爱过的蛋挞 · 用python开启临时http服务器及其ip ...· 2 年前 ·

慷慨的匕首 · paddlespeech asr 使用教程 ...· 2 年前 ·

儒雅的炒饭 · 如何共同安装composer ...· 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用户