Exact fetch returns more than requested number of rows while executing Stored Procedure

Question

I am trying to write a stored procedure that will accept Item Number as input parameter and will return some data against it. I am not able to call the procedure as it keeps on giving error that number of rows are more than requested. I am new to Oracle and it not making sense that why it is working if I am returning 1 row only and not when the data is in multiple rows ? I am pasting my code below to see if someone can point out where I am not going right.

CREATE OR REPLACE PROCEDURE AGILE.SELECT_ITEM 
(
ITEM_NO IN VARCHAR2, ITEM_NUMBER OUT VARCHAR2, REV_NUMBER OUT VARCHAR2, CHANGE_NUMBER OUT VARCHAR2, SRC_FILENAME OUT VARCHAR2, DST_FILENAME OUT VARCHAR2,
FILEPATH OUT VARCHAR2, DESCRIPTION OUT VARCHAR2
)
IS 
BEGIN 

WITH CTE AS(
SELECT 
RANK () OVER (PARTITION BY ITEM_NUMBER ORDER BY NVL(c.id, -1) DESC) RN,
CTE.ITEM_NUMBER, NVL(REV.REV_NUMBER,'Introductory') REV_NUMBER, NVL(C.CHANGE_NUMBER,'NOCHANGE') CHANGE_NUMBER,
CASE WHEN AM.FILE_ID = 0 THEN VM.FILE_ID ELSE AM.FILE_ID END AMVMFILE_ID, A.DESCRIPTION

FROM ITEM CTE 
LEFT JOIN AGILE.REV REV ON REV.ITEM = CTE.ID 
LEFT JOIN AGILE.CHANGE C ON C.ID = REV."CHANGE" 
LEFT JOIN AGILE.ATTACHMENT_MAP AM ON AM.PARENT_ID = CTE.ID AND C.ID = AM.PARENT_ID2
LEFT OUTER JOIN AGILE.ATTACHMENT A ON AM.ATTACH_ID = A.ID AND AM.LATEST_VSN = a.LATEST_VSN
LEFT OUTER JOIN AGILE.VERSION_FILE_MAP VM ON (AM.VERSION_ID = VM.VERSION_ID OR AM.LATEST_VSN = VM.VERSION_ID)
LEFT OUTER JOIN AGILE."VERSION" V1 ON V1.id = VM.VERSION_ID 

WHERE CTE.ITEM_NUMBER = 'AGY-731946-0000'

AND NVL(REV.EFFECTIVE_DATE, TO_DATE('2020-04-17', 'YYYY-MM-dd')) <= TO_DATE('2020-04-17', 'YYYY-MM-dd')
AND  (C.SUBCLASS IS NULL OR C.SUBCLASS NOT IN 
  (
  11141
  ,434284
  ,1455
  ,313304
  ,190161
  ,435727
  ,43556
  ,181524
  ,181518
  ,434124
  ,435796
  ,8141
  ,7141
  ,341469
  ,434038
  ,435834
  ,408376
  ))
), FINALCTE AS(

SELECT ITEM_NUMBER, REV_NUMBER, CHANGE_NUMBER,
    'agile'||f.id||'.'||f.file_type src_filename,
    f.filename dst_filename,
    NVL(replace(fi.ifs_filepath,'\','/'),
    SUBSTR(LPAD(to_char(f.id),11,'0'),0,3)||'/'||
    SUBSTR(LPAD(to_char(f.id),11,'0'),4,3)||'/'||
    SUBSTR(LPAD(to_char(f.id),11,'0'),7,3)||'/'||
    'agile'||f.id||'.'||f.file_type) filepath, DESCRIPTION
FROM CTE DC
LEFT OUTER JOIN FILES F ON DC.AMVMFILE_ID = F.ID
LEFT OUTER JOIN FILE_INFO FI ON FI.FILE_ID = DC.AMVMFILE_ID
WHERE RN = 1 AND AMVMFILE_ID IS NOT NULL

ORDER BY ITEM_NUMBER, RN
)
SELECT DISTINCT 
LTRIM(RTRIM(ITEM_NUMBER)) ITEM_NUMBER, LTRIM(RTRIM(REV_NUMBER)) REV_NUMBER, LTRIM(RTRIM(CHANGE_NUMBER)) CHANGE_NUMBER, 
LTRIM(RTRIM(src_filename)) src_filename, LTRIM(RTRIM(dst_filename)) dst_filename, LTRIM(RTRIM(filepath)) filepath, 
LTRIM(RTRIM(DESCRIPTION)) DESCRIPTION
INTO ITEM_NUMBER, REV_NUMBER, CHANGE_NUMBER, SRC_FILENAME, DST_FILENAME, FILEPATH, DESCRIPTION
FROM FINALCTE
--SELECT ITEM_NUMBER INTO ITEM_NUMBER FROM ITEM 
WHERE ITEM_NUMBER = ITEM_NO;
END;

The query when run separately returns 2 rows of data but when I call this procedure it is not retunrning data but an error. This is how I am calling my procedure.

CALL AGILE.SELECT_ITEM('AGY-731946-0000',?,?,?,?,?,?,?);

Please help me out as I am stuck badly.

Expected output.

ITEM_NUMBER    |REV_NUMBER|CHANGE_NUMBER|SRC_FILENAME    |DST_FILENAME                                                                             |FILEPATH                    |DESCRIPTION                    |
---------------|----------|-------------|----------------|-----------------------------------------------------------------------------------------|----------------------------|-------------------------------|
AGY-731946-0000|A         |DL000208     |agile1820829.pdf|Cert_BSMI_S-LK5.pdf                                                                      |000/018/208/agile1820829.pdf|AGNCY CERTI BSMI 3892A547 S-LK5|
AGY-731946-0000|A         |DL000208     |agile1820830.url|HTTP://AgileArchive.logitech.com/WWDocLib2/WWDL002.nsf/0/C125679800434FCCC12568F100392326|000/018/208/agile1820830.url|AGNCY CERTI BSMI 3892A547 S-LK5|

Error:

SQL Error [1422] [21000]: ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "AGILE.SELECT_ITEM", line 9

Please give exact ORA-XXXX error message. For ORA-1422 see https://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows — pifor, Jun 12 '20 at 13:45
You cannot assign multiple values to a scalar variable. Either you need to use record or loop through the cursor. But we need more details about what are you trying to achieve. — Lalit Kumar B, Jun 12 '20 at 13:46
@pifor I already looked at the link you provided but couldn't get what's happening there or here. as I said i am not familiar with Oracle much yet. can you help with this query ? — Muhammad Asim, Jun 12 '20 at 13:47
@LalitKumarB I have just updated the question and added data that I am expecting. — Muhammad Asim, Jun 12 '20 at 13:48

score 1 · Answer 1 · answered Jun 12 '20 at 13:53

Root cause it that your top level SQL query returns 2 rows.

For your code to work it must only return 1 row. Here is short demo:

SQL> create table t(x int);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> --
SQL> create or replace procedure myproc
  2  is
  3   l number;
  4  begin
  5  select x into l from t;
  6  end;
  7  /

Procedure created.

SQL> show errors
No errors.
SQL> exec myproc
BEGIN myproc; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TESTDBA.MYPROC", line 5
ORA-06512: at line 1


SQL> --
SQL> delete t where x=2;

1 row deleted.

SQL> select * from t;

     X
----------
     1

SQL> exec myproc;

PL/SQL procedure successfully completed.

SQL>

In this case you *must* modify your code as recommended by the other answer: your code should be able to process these 2 rows if it is expected that the query must return 2 rows. — pifor, Jun 12 '20 at 13:58

score 1 · Answer 2 · answered Jun 12 '20 at 13:53

I am new to Oracle and it not making sense that why it is working if I am returning 1 row only and not when the data is in multiple rows ? The query when run separately returns 2 rows of data but when I call this procedure it is not retunrning data but an error.

You cannot assign multiple values to a scalar variable. The SELECT..INTO clause that you are using to assign multiple rows will error out. You could do it in following ways:

Use a CURSOR FOR LOOP. But it would be slow as it is row-by-row operation.
Use record/collection, see documentation for more details https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-collections-and-records.html#GUID-7115C8B6-62F9-496D-BEC3-F7441DFE148A

Exact fetch returns more than requested number of rows while executing Stored Procedure

2 Answers2