sql - Checking conflicting data with SELECT and IF statements in a procedure -

i using select statement select conflicting treatments prescription table have , using if statement check selected data against data entering when running procedure. expect procedure disallow same client book conflicting treatments on same day. when run procedure getting error "exact fetch returns more requested number of rows". if run select statement on own, works explained above. here procedure:

create or replace procedure fpresc (     fp_id varchar2,     ftreat_id varchar2,     fclient_id varchar2,     fdoc_id varchar2,     fp_date date)    v_client_id prescription.client_id%type;    v_conflict treatments.conflict%type;    v_p_date prescription.p_date%type;    v_treat_id treatments.treat_id%type; begin     select p.client_id, t.conflict, p.p_date, t.treat_id     v_client_id, v_conflict, v_p_date, v_treat_id     prescription p, treatments t     p.treat_id=t.treat_id     , t.conflict not null;      if fp_date = v_p_date , fclient_id = v_client_id , ftreat_id = v_treat_id           dbms_output.put_line('conflict');     else          insert prescription (p_id, treat_id, client_id, doc_id, p_date)          values (fp_id, ftreat_id, fclient_id, fdoc_id, fp_date);     end if; end fpresc; / 

and example of data trying enter new prescription (note: data conflicting, should throw error when entered):

 exec fpresc ('p00011', 't016', 'c00017', 'd006', '28-nov-14'); 

some insight appreciated, if possible

"exact fetch returns more requested number of rows" pretty clear message, , refers select statement. using select ... into query must return 1 row, because specify 1 set of variables. message suggests query returns more 1 row.

i think best solution checks in query itself. can return wether or not query matches (in case there conflict). query return 1 record 1 or 0 indicated conflict or not, if subquery checks returns more 1 conflict.

select     case when exists         (select 'x'                    prescription p           inner join treatments t on t.treat_id = p.treat_id                    fp_date = p.p_date            , fclient_id = p.client_id            , ftreat_id = t.treat_id            , t.conflict not null)         1     else         0     end     v_exists dual;  if v_exists = 1     raise_application_error(-20000, 'conflict'); -- actual exception, if else     insert prescription (p_id, treat_id, client_id, doc_id, p_date)          values (fp_id, ftreat_id, fclient_id, fdoc_id, fp_date); end if; 

by way, if need read more 1 row query, can either open cursor, process 1 one. use for loop easy access.

or can use bulk collect into read data single table-like variable.