Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: stored procedure don't run.........

    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;
    <<FINE>>
    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!!!

  2. #2
    Join Date
    Jun 2004
    Posts
    2

    Thumbs up

    The answer is in the Where Clause of the SQL

    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));

    I think it should be as follows

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

    HTH

    Regards,
    Engi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •