I have 3 table called List1. List 2, and List3
The columns in List1 are ID, Name
The columns in List2 are ID (Foreign Key references List1), companyID (Foreign Key references List1)
The columns in List3 are companyID, companyName

I want to write a producere to insert a person to List1 and List2 and then ensure that the companyID is valid (between 1-10), and if its invalid i have to rollback the person insert operation

PLEASE HELP!

I think you should at first evaluate the company-id from table3 and THEN insert the person and the link in table2. so you don't need rollback or delete...

function get_company_id (p_name in varchar2) return number
is
l_n_ret number;
begin
select id
into l_n_ret
from table3
where company_name = p_name;

return l_n_ret;

exception when others then return 0;
end;


procedure insert_person(p_comp_name in varchar2.p_pers_name in varchar2)
is
l_comp_id number;
begin
l_comp_id:=get_company_id(p_comp_name);
if l_comp_id > 0
then
insert the data in table1 and 2
commit;
end if;
end;