Reputation: 57

ORA-06512:exact fetch returns more than requested number of rows

While running the below procedure I am getting the below error

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

Could you please suggest me how I could assign fetched data into a variable? I suspect variable (lnbuf) of data type varchar2 is not accepting row fetched by the select statement. Please help me in solving this error

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 ;

Upvotes: 1

Answers (3)

Kishan Mashru

Reputation: 1

The error is encountered in a Select Into statement when the select statement returns more than one row. As the static variables can store only one value in them, what we need to do is either change the where clause of the select statement to fetch only one record or use the query in a for loop.

Upvotes: 0

XING

Reputation: 9886

As suggested by the error, and explained below that your select query is returning more than 1 rows and you are trying to store that in a variable, which is cuasing the issue. Change your loop as below:

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;

Upvotes: 0

Aleksej

Reputation: 22979

You can not fetch more than one row into a scalar variable; for example:

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

If you need a single value, you must be sure that your query only returns one row:

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

If you need to get more than one row, you can use a BULK COLLECT and appropriate variables:

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
Sundar

Upvotes: 3

ORA-06512:exact fetch returns more than requested number of rows

Answers (3)

Related Questions