create or replace procedure insert_state1(p_country_id in state.countryid%type, p_state_name in state.statename%type) is
v_countryname varchar2(20);
begin
select countryname into v_countryname from country where countryid=p_country_id;
insert into state(countryid, countryname, statename) values(p_country_id,v_countryname,p_state_name);
end;
/
/
Procedure created.
SQL> exec insert_state1(10,'ap');
BEGIN insert_state1(10,'ap'); END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.INSERT_STATE1", line 4
ORA-06512: at line 1
where is error and how to execute?
where is error and how to execute?
Shankar MPosted Nov 27, 2012, 4:31 AM
Hi Ramesh,
You can use the TOO_MANY_ROWS exception handler to handle too many rows or use cursors to overcome the too many rows error.
Thanks,
Shankar
rameshPosted Aug 25, 2012, 2:33 AM
Javeed M ShaikhPosted Aug 24, 2012, 3:59 PM
select countryname into v_countryname from country where countryid=p_country_id;