-1

I am trying to create a member function that returns the member names of expired members. My select query works outside of the member function and this member function compiles with no problems but when I call the function I get this error:

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

I assume it is something simple but I have not been using Oracle for long so got a bit stuck with syntax. Can anyone help with this?

Daveloper87
  • 686
  • 1
  • 8
  • 13
  • Is there a particular reason for going down this object-oriented route, rather than just using the database relationally? – David Aldridge May 04 '13 at 09:35
  • 1
    Why did you remove the code sample? The question is utterly meaningless without it. – APC May 04 '13 at 14:37

2 Answers2

1

Since this query can return more than one row you need to use a cursor and a cursor-for loop to iterate over results. However you cannot return more than one member name using a varchar; you could use a PL-SQL table.

Pino
  • 6,292
  • 5
  • 42
  • 54
0

select m.forename into memberName from member_tab m where ADD_MONTHS(m.datejoined, 64) < SYSDATE;

looks like this query is returning more than 1 results ...

u can use a select count(*) or exception handling to address the exception and handle >1 results

Ashish Thukral
  • 1,288
  • 12
  • 26
  • Thanks for this, I have tried the count but I still get duplicated rows? Do you know how to get a count to only display one row? – Daveloper87 May 04 '13 at 09:52
  • u can use count to check if there are more than 1 rows not to fix it. do you intend to return all such values or just 1 value from the function? – Ashish Thukral May 04 '13 at 10:02