ORA-01422: exact fetch returns more than requested... 1

[hanchilicious]

Hi,

I'm trying to run some pl/sql which is returning the above error.

I'm not much of an oracle developer, and I'm just trying to resolve this issue in our programmers absence because it's an live problem. The bulk of the code is essentially populating the ListingData table below, but I think it's the following which is causing the problem:

  IF vCount = 0 THEN
    c_ResultSet := GetEmptyResultSet('');
  ELSE
    OPEN   c_ResultSet FOR
    SELECT House,
           Type,
           Title,
           SecondTitle,
           Channel,
           ValidFromDate,
           ValidToDate,
           ValidFromTime,
           ValidToTime,
           ValidDays
    FROM   ListingData
--    WHERE rownum <= 1
    ORDER BY 1;
  END IF;

The problem occurs with or without the rownum line. I guess this happens because there is more than one row being returned, but how do I handle this to return multiple rows - or even just return one of a duplicate series of rows?

Please help if you can!

Handwringingly yours,
H

 

[LKBrwnDBA]

It may not be that statement, look for the line number corresponding to the error and post that statement.

This error occurs when the result from a query is expected to return one row of data only, normally in a SELECT..INTO statement or an UPADATE...SET colx=(SELECT...) construct among others.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[hanchilicious]

Hiya,

I get multiple line numbers flagged up. They're highlighted with --><--:

1) is from a function called by the proc:

FUNCTION GetId(v_Item_id IN NUMBER)  RETURN varchar2 
IS
strId VARCHAR2(50);
BEGIN
strId := ' ';
---> select distinct m.device_code into strId <--
...

2) is from the proc itself:

-->  INSERT INTO ListingData ( <--

         House,   Type,          Title, SecondTitle,
         Channel,       ValidFromDate, ValidToDate, 
         ValidFromTime, ValidTotime,   ValidDays,
         Item_id,      Item_type)
  SELECT DISTINCT ...etc

Thanks for looking at this.
H

 

[LKBrwnDBA]

Most likely it's the first one.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[hanchilicious]

Okay, but how do I get the procedure to accept that there may be multiple values returned by the function?

 

[hanchilicious]

To be fair, I think the 'distinct' included in both statements shouldn't let multiple returned values be an issue.

 

[SantaMufasa]

hanchilicious,

And to be additionally fair, if there are 47 distinct m.device_code values out of 352 rows, those 47 distinct values cannot all reside simultaneously in "strID".

If you must use a "SELECT...INTO", then you can do a "SELECT MAX(m.device_code) INTO strID..." (or MIN...) to guarantee a single value. Otherwise, if you wish to process all DISTINCT values, then I recommend a "CURSOR FOR LOOP" construction that allows you to process through an unlimited number of returned values.

Let us know your thoughts on this topic.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hanchilicious]

Thanks Santa.

I addressed this by adding an additional parameter to the function, to promote uniqueness. And whilst this seems to have resolved the 01422 error, now, the procedure returns 'ORA-01722: invalid number' when attempting to insert into a temp table. There is only one numerical column, and I tried re-running passing a static 1 into the column, and it still returned the same error.

The only thing I can think of is that it is that it is coming from the exception handler:

EXCEPTION
WHEN OTHERS THEN
c_ResultSet := GetEmptyResultSet('ERROR');
vMsg := 'Error: ' || SQLCODE || '- ' || SUBSTR(SQLERRM, 1 , 64);
Report_Logger.LogMessage(vProcName, vTerritory, vUnique, USER, 'Y', vMsg);

where c_ResultSet is looking for a number assignment.

Can you help me find where to start looking?

 

[hanchilicious]

I meant to paste in the precursor to the handler:

-- Return Results
  IF vCount = 0 THEN
    c_ResultSet := GetEmptyResultSet('');
  ELSE
    OPEN   c_ResultSet FOR
    SELECT House,
           Type,
           Title,
           SecondTitle,
           Channel,
           ValidFromDate,
           ValidToDate,
           ValidFromTime,
           ValidToTime,
           ValidDays
    FROM   ListingData
    ORDER BY 1;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    c_ResultSet := GetEmptyResultSet('ERROR');
    vMsg := 'Error: ' || SQLCODE || '- ' || SUBSTR(SQLERRM, 1 , 64);
    Report_Logger.LogMessage(vProcName, vTerritory, vUnique, USER, 'Y', vMsg);

GetEmptyResultSet =

FUNCTION GetEmptyResultSet(vValue IN VARCHAR2) RETURN t_ErrorSet IS
  l_Result t_ErrorSet;
BEGIN
  OPEN l_Result FOR
    SELECT NULL as House,
           NULL as Type,
           decode(vValue, 'ERROR', 'Error', NULL) as Title,
           NULL as SecondTitle,
           NULL as Channel,
           NULL as ValidFromDate,
           NULL as ValidToDate,
           NULL as ValidFromTime,
           NULL as ValidToTime,
           NULL as ValidDays
      FROM sys.dual
     WHERE 1 = 0 
     OR    vValue = 'ERROR' ;
     
  RETURN(l_Result);
  
END GetEmptyResultSet;

The invalid number error highlights the insert into temp table line. What is its bug bear?

 

[hanchilicious]

Never mind, sorted it.

 

[SantaMufasa]

Whoa, hanchilicious..."sorted it" doesn't "sort it" for thread readers that come after us. <smile>

Please post the details of what the problem is/was and how your resolved it.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]