HI,
I have these tables:

SITE_D:
SITE_ID......VARCHAR2(32)......PK
NAME.........VARCHAR2(64)

BL_D:
SITE_ID......VARCHAR2(32)......FK ON SITE_ID(SITE)
BL_ID........VARCHAR2(32)......PK
NAME.........VARCHAR2(64)

FL_D:
SITE_ID......VARCHAR2(32).....FK ON SITE_ID(SITE)
BL_ID........VARCHAR2(32)......PK1
FL_ID........VARCHAR2(32)......PK2
NAME.........VARCHAR2(64)

AND TABLES: SITE, BL, FL with same columns and constraints of SITE_D, BL_D, FL_D

my data are:
site_d
site_id............name
001................AAAA
002................BBBB

bl_d
site_id.........bl_id.......name
001.............001.........AAAA
002.............002.........BBBB

fl_d
site_id.........bl_id.......fl_id........name
001..............001.........A01.........AAAA
001..............001.........A02.........AAAA
001..............001.........A03.........AAAA
001..............001.........A04.........AAAA
002..............002.........D01.........BBBB
002..............002.........D09.........BBBB

I'd like to create a stored procedure that recovers just one floor from table FL_D and insert any value in the tables: site, bl, fl
for example, if I want recover fl_id=A04 bl_id=001 I must insert into fl (site_id=001, bl_id=001, fl_id=A04) - insert into bl (bl_id='001', site_id='001') - insert into site (site_id='001') and finally delete from fl_d, bl_d, site_d

I tried this:

CREATE OR REPLACE PROCEDURE BL_FLOOR (BUILD_IN VARCHAR2, FLOOR_IN VARCHAR2) AS
BEGIN
DECLARE
VAR_FL_ID FL.FL_ID%TYPE;
VAR_BL_ID BL.BL_ID%TYPE;
VAR_SITE_ID SITE.SITE_ID%TYPE;

CURSOR CURSORE IS
SELECT SITE_ID,
BL_ID,
FL_ID
FROM FL_D
WHERE BUILD_IN = BL_ID
and FLOOR_IN = FL_ID;

BEGIN
OPEN CURSORE;
LOOP
FETCH CURSORE INTO VAR_SITE_ID,VAR_BL_ID,VAR_FL_ID;
EXIT WHEN CURSORE%NOTFOUND;

BEGIN
INSERT INTO SITE
(SELECT *
FROM SITE_D
WHERE SITE_ID = VAR_SITE_ID
AND NOT EXISTS
(SELECT *
FROM SITE
WHERE SITE_ID = SITE_D.SITE_ID));
COMMIT;
END;
BEGIN
INSERT INTO BL
(SELECT *
FROM BL_D
WHERE BL_ID = VAR_BL_ID
AND NOT EXISTS
(SELECT *
FROM BL
WHERE BL_ID = BL_D.BL_ID));

END;
BEGIN
INSERT INTO FL
(SELECT *
FROM FL_D
WHERE FL_ID = VAR_FL_ID
AND NOT EXISTS
(SELECT *
FROM FL
WHERE FL_ID = FL_D.BL_ID
AND FL_ID = FL_D.FL_ID));

END;
BEGIN
DELETE FL_D
WHERE FL_ID = VAR_FL_ID;
COMMIT;
DELETE BL_D
WHERE BL_ID = VAR_BL_ID;
COMMIT;
DELETE SITE_D
WHERE SITE_ID = VAR_SITE_ID;
COMMIT;
END;
END LOOP;
<>
CLOSE CURSORE;
END;
END;
/

DECLARE
BUILD_IN VARCHAR2(200);
FLOOR_IN VARCHAR2(200);

BEGIN
BUILD_IN := '001';
FLOOR_IN := 'A04';

BL_FLOOR ( BUILD_IN, FLOOR_IN );
COMMIT;
END;
but don't run correctly:
I get error:
ORA-02291: integrity constraint violated - parent key not found


How can I recover my data with a stored procedure??
Thanks in advance!!!