inserting (+update) into multiple tables from query without losing reference

Question

I have this problem:

I have 3 tables - RL_EST_SH (ID,PARTY_ID), PARTY(ID), COMM_PROP(PARTY_ID,PERSID1,PERSID2) tables have other columns, but they are irrelevant.

RL_EST_SH is filled with IDs and other two are empty.

Than I have a query, select that returns me this result set (showing only first 10 rows):

REAL_ESTATE_SHARE_ID        PERSON_ID1                     PERSON_ID2                  
"4000"                        "17839"                       "17838"                       
"41572"                       "47306"                       "47305"                       
"41573"                       "47306"                       "47305"                       
"41765"                       "47712"                       "47711"                       
"41757"                       "47714"                       "47713"                       
"41803"                       "47716"                       "47715"                       
"41903"                       "47718"                       "47717"                       
"41907"                       "47720"                       "47719"                       
"41910"                       "47720"                       "47719"                       
"41713"                       "47720"                       "47719"                       

This query gives me two owners of REAL_ESTATE_SHARE_ID

Now here is my problem:

For every REAL_ESTATE_SHARE_ID in the result set of my query I need to create new Party (ID in table Party) using sequence P0_PARTY_S, assign this new ID to PARTY_ID column in RL_EST_SH for for IDs from query and create new record in COMM_PROP - record that keeps owners of REAL_ESTATE_SHARE_ID through PARTY_ID.

For better understanding here is simple draw:

I don't know how to do this without losing reference or how to do this at once. I was trying to user cursor, but I am not experienced enough to do so. I guess it is the right way but I don't know how.

Answer 1

Well, this is how far I am now...

declare 
REAL_ESTATE_SHARE_ID varchar2(19);
PERSON_ID1 VARCHAR2(19);
PERSON_ID2 VARCHAR2(19);
PERS_ID varchar2(19);
CURSOR C1 IS myquery;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO REAL_ESTATE_SHARE_ID, PERSON_ID1, PERSON_ID2;
EXIT WHEN C1%NOTFOUND;
PERS_ID := P0_PARTY_S.nextval;
INSERT INTO P0_PARTY VALUES (PERS_ID,0,sysdate,':party.Party:party.Person:',sysdate,2,null,'SJM',93407656999);
INSERT INTO P5_COMMUNITY_PROPERTY VALUES (P5_COMMUNITY_PROPERTY_S.nextval,'Y',PERS_ID,PERSON_ID1, PERSON_ID2);
UPDATE BIZZTST.R0_RL_EST_SHARE set PARTY_ID = PERS_ID where BIZZTST.R0_RL_EST_SHARE.ID = REAL_ESTATE_SHARE_ID;
END LOOP;
CLOSE C1;
END;

This is causing `00001. 00000 - "unique constraint (%s.%s) violated" on this row:

INSERT INTO P5_COMMUNITY_PROPERTY VALUES (P5_COMMUNITY_PROPERTY_S.nextval,'Y',PERS_ID,PERSON_ID1, PERSON_ID2);

The third row has primary key constraint. Is it possible that my code is wrong and I am inserting same ID every time? How can I fix it? Or my whole code is wrong?