PL/SQL Insert with cursor

Asked

Viewed 1,449 times

0

Everybody, good afternoon, everybody.

I have 2 tables, one of foro_tribunal and another of vara In the table of vara, need to include 111 records by foro_tribunal, so I created a foro_tribunal as a model to copy to others foro_tribunais when you walk through the loop. Follow the code:

SET SERVEROUTPUT ON;
DECLARE 
    V_FORO_MODELO     NUMBER (019) := 12057 ;--MODELO A SER COPIADO(FORO_TRIBUNAL_ID);
    V_USUARIO         NUMBER(019);--USUARIO_ID
    SQ_VARA           NUMBER(019);

    CURSOR CURSOR_FORO_TRIBUNAL IS
        SELECT FORO_TRIBUNAL_ID FROM FORO_TRIBUNAL WHERE FORO_TRIBUNAL_ID <> V_FORO_MODELO AND FORO_TRIBUNAL_ID IN (564) ; --PARA(FORO_tRIBUNAL_ID)

    CURSOR CURSOR_VARA IS
        SELECT VARA_ID,NOME,ORGANIZATION_ID,status FROM VARA WHERE FORO_TRIBUNAL_ID =  V_FORO_MODELO;

BEGIN
    V_USUARIO := 2;
    SQ_VARA := VARA_SEQ.NEXTVAL;

    FOR CFT IN CURSOR_FORO_TRIBUNAL LOOP
        FOR CV IN CURSOR_VARA LOOP
            INSERT INTO VARA(VARA_ID,DATA_REGISTRO,NOME,STATUS,FORO_TRIBUNAL_ID,ORGANIZATION_ID,USUARIO_ID) 
            VALUES(SQ_VARA,SYSDATE,CV.NOME,CV.STATUS,CFT.FORO_TRIBUNAL_ID,CV.ORGANIZATION_ID,V_USUARIO);
        END LOOP;
    END LOOP;
END;

But when I run, it gives the error :

Error report - ORA-00001: exclusive restriction (SYSTEM.SYS_C007029) violated ORA-06512: online 18 00001. 00000 - "Unique Constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to Insert a Duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a Duplicate entry exists at a Different level. *Action: Either remove the Unique Restriction or do not Insert the key.

What change should I make to run without errors ?

oracle pl-sql oracle12c
  • 2

    without seeing the structure of the tables can not answer, but the error is clear, tried to insert more than one value with the same key. Before Insert, try using a dbms_output.put_line to list the values being used in Insert and see where you are duplicating

  • Thanks Ricardo ! Just includes the SQ_VARA := VARA_SEQ.NEXTVAL; inside the loop and everything worked out. I was including the 111 items for the same id, but now it worked. Vlw !!

1 answer

0


Solved!

SET SERVEROUTPUT ON;
DECLARE 
    V_FORO_MODELO     NUMBER (019) := 12057 ;--MODELO A SER COPIADO(FORO_TRIBUNAL_ID);
    V_USUARIO         NUMBER(019);--USUARIO_ID
    SQ_VARA           NUMBER(019);
    V_CONTADOR        NUMBER(019);
    CURSOR CURSOR_FORO_TRIBUNAL IS
        SELECT FORO_TRIBUNAL_ID FROM FORO_TRIBUNAL WHERE FORO_TRIBUNAL_ID <> V_FORO_MODELO AND FORO_TRIBUNAL_ID;
    CURSOR CURSOR_VARA IS
        SELECT VARA_ID,NOME,ORGANIZATION_ID,status FROM VARA WHERE FORO_TRIBUNAL_ID =  V_FORO_MODELO;  
BEGIN
    V_USUARIO := 2;
    V_CONTADOR:= 0;
    FOR CFT IN CURSOR_FORO_TRIBUNAL LOOP
        FOR CV IN CURSOR_VARA LOOP
            SQ_VARA := VARA_SEQ.NEXTVAL;
           INSERT INTO VARA(VARA_ID,DATA_REGISTRO,NOME,STATUS,FORO_TRIBUNAL_ID,ORGANIZATION_ID,USUARIO_ID)  VALUES(SQ_VARA,SYSDATE,CV.NOME,CV.STATUS,CFT.FORO_TRIBUNAL_ID,CV.ORGANIZATION_ID,V_USUARIO);
           V_CONTADOR:= V_CONTADOR+1;
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Qtd Inserida: '|| V_CONTADOR);
END;

Browser other questions tagged oracle pl-sql oracle12c

You are not signed in. Login or sign up in order to post.