0

i am trying to create a sql function that prints the reservations made by a customer when his name is beeing given. The customers names are in a table called CLIENTS.FNAME CLIENTS.MNAME CLIENTS.LNAME and the reservations searched by an id- foreign key there is the code for the function that shows a 'too many values" error, maybe it has to be done by Loop or something?

create or replace FUNCTION cl_reserv(clName VARCHAR2)

RETURN RESERVATIONS%ROWTYPE

IS
resRecord RESERVATIONS%ROWTYPE;

BEGIN
  SELECT RESID,STARTDATE,ENDDATE,ADDINFO INTO resRecord
           FROM RESERVATIONS INNER JOIN CLIENTS ON RESERVATIONS.CLIENTID=CLIENTS.CLIENTID
           WHERE clName IN (FNAME,MNAME,LNAME);
RETURN resRecord;
END;
Ivan M
  • 209
  • 1
  • 2
  • 9
  • 1
    the select returns more than one record, you try to insert the list of records into one variable - and server raises the exception – are Jan 17 '17 at 11:22
  • 1
    see this article "how to return collection form pl/sql function" http://stackoverflow.com/questions/7888990/return-collection-from-packaged-function-for-use-in-select – are Jan 17 '17 at 11:24

1 Answers1

2

Your SELECT clause doesn't match with the record you're trying to select into. Change your code like this:

CREATE OR REPLACE FUNCTION cl_reserv(clName VARCHAR2)
    RETURN RESERVATIONS%ROWTYPE 
IS
    resRecord RESERVATIONS%ROWTYPE;

BEGIN
  SELECT r.* INTO resRecord
           FROM RESERVATIONS r INNER JOIN CLIENTS c ON r.CLIENTID=c.CLIENTID
           WHERE clName IN (FNAME,MNAME,LNAME);
    RETURN resRecord;
END cl_reserv;

By using r.*, you select exactly the number, type and sequence of columns that are needed for the record type.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • 2
    or alternatively, you could specify the record fields to be returned into, e.g. `SELECT RESID,STARTDATE,ENDDATE,ADDINFO INTO resRecord.resid, resRecord.startdate, resRecord.enddate, resRecord.addinfo from ...` – Boneist Jan 17 '17 at 11:44
  • thank you, for the help. When i am calling the function i am still getting error regarding the result. i am trying to get the lines with information from resRecord. 'code' DECLARE res RESERVATIONS%ROWTYPE; BEGIN cName := cl_reserv('Ivan'); DBMS_OUTPUT.PUT_LINE(res); END; 'code' – Ivan M Jan 17 '17 at 12:47