SQL Error: ORA-01422: the exact extraction returns more than

[danilosantos]

Hello everyone, I'm having the following problem in Oracle:

Select all

SQL Error: ORA-01422: a extração exata retorna mais do que o número solicitado de linhas 
ORA-06512: em "PROJE.TRG_TAGVALUE_CONTROLE_INS", line 6 
ORA-04088: erro durante a execução do gatilho 'PROJE.TRG_TAGVALUE_CONTROLE_INS' 
01422. 00000 -  "exact fetch returns more than requested number of rows" 
*Cause:    The number specified in exact fetch is less than the rows returned. 
*Action:   Rewrite the query or change number of rows requested

follows code of my trigger

Select all

create or replace 
TRIGGER TRG_TAGVALUE_CONTROLE_INS 
AFTER INSERT 
ON POC_TAGVALUE 
FOR EACH ROW 
DECLARE  
ID_CONTR_SEQ NUMBER; 
BEGIN 
 IF :new.NM_COLUN = 'NM_TAGPA' THEN 
     
    SELECT ID_CONTR INTO ID_CONTR_SEQ 
    FROM POC_CONTROLE  
    WHERE DT_FINAL IS NULL AND  
          FL_TIPO = 'P' AND  
          NM_TAG  = :new.NM_TAG AND  
          ID_EQUIP = :new.ID_EQUIP AND  
          ID_CENTR = :new.ID_CENTR AND  
          ID_GRUPO = :new.ID_GRUPO; 
     
    IF (ID_CONTR_SEQ IS NULL AND :new.VL_TAG = 0) THEN  
       
      -- PEGAR NUMERO SEQUENCE 
      SELECT SEQ_ID_CONTR.NEXTVAL INTO ID_CONTR_SEQ FROM dual;  
  
      -- INSERE NOVO CONTROLE     
      INSERT INTO POC_CONTROLE  
      ( ID_CONTR, 
        ID_EQUIP, 
        ID_CENTR, 
        ID_GRUPO, 
        DT_INICI, 
        FL_TIPO, 
        VL_PERDA, 
        NM_TAG 
      ) VALUES ( 
        ID_CONTR_SEQ, 
        :new.ID_EQUIP, 
        :new.ID_CENTR, 
        :new.ID_GRUPO, 
        :new.DT_TAG_VALUE, 
        'P', 
        0, 
        :new.NM_TAG 
      );     
 
    ELSE 
     
      -- ATUALIZA TABELA CONTROLE  
      UPDATE POC_CONTROLE SET DT_FINAL = :new.DT_TAG_VALUE  
      WHERE ID_CONTR = ID_CONTR_SEQ;    
             
    END IF;     
     
 END IF; 
  
 IF :new.NM_COLUN = 'NM_TAGPE' THEN  
  
    INSERT INTO POC_CONTROLE  
    ( ID_CONTR, 
      ID_EQUIP, 
      ID_CENTR, 
      ID_GRUPO, 
      DT_INICI, 
      FL_TIPO, 
      VL_PERDA, 
      NM_TAG 
    ) VALUES ( 
      ID_CONTR_SEQ, 
      :new.ID_EQUIP, 
      :new.ID_CENTR, 
      :new.ID_GRUPO, 
      :new.DT_TAG_VALUE, 
      'R', 
      :new.VL_TAG, 
      :new.NM_TAG 
    );     
     
 END IF; 
 
END;

To test the trigger I am running the following command:

Select all

INSERT INTO TAB_TAGVALUE  
    (ID_EQUIP, 
     ID_GRUPO, 
     ID_CENTR, 
     DT_TAG_INTER, 
     DT_TAG_VALUE, 
     NM_TAG, 
     VL_TAG, 
     NM_COLUN 
     ) VALUES  
    ('MP09', 
     'GR1', 
     'WK1', 
     SYSDATE, 
     SYSDATE, 
     'Line1Status', 
     1000, 
     'NM_TAGPA');

Thanks from now .... what I do to solve this problem guys??????

Danilo

[fsitja]

Your SELECT INTO is returning more than one line and "bursts" the fetch.

is probably the query below:

Select all

 
SELECT ID_CONTR INTO ID_CONTR_SEQ 
    FROM POC_CONTROLE 
    WHERE DT_FINAL IS NULL AND 
          FL_TIPO = 'P' AND 
          NM_TAG  = :new.NM_TAG AND 
          ID_EQUIP = :new.ID_EQUIP AND 
          ID_CENTR = :new.ID_CENTR AND 
          ID_GRUPO = :new.ID_GRUPO;  

[danilosantos]

More like I do to solve this problem?

[SergioLBJr]

Check your queys.

tests all of them in the bank, and find a (s) who return more than one line, by finding it Check that Join is correct (if no field in Join), if Yes then post in the forum a problematic query, the PKs of the tables in which it searches for the data and the desired select result.

[danilosantos]

Sergio thanks for the help ... more at the beginning of the post I put the error message

Select all

SQL Error: ORA-01422: a extração exata retorna mais do que o número solicitado de linhas  
ORA-06512: em "PROJE.TRG_TAGVALUE_CONTROLE_INS", line 6  
ORA-04088: erro durante a execução do gatilho 'PROJE.TRG_TAGVALUE_CONTROLE_INS'  
01422. 00000 - "exact fetch returns more than requested number of rows"  
*Cause: The number specified in exact fetch is less than the rows returned.  
*Action: Rewrite the query or change number of rows requested 

the problem is in the select below

[1]]

I would like to know how to solve this problem inside the trigger do you know? I need to get the value of a column in the database using the Select ID_CONTR INTO ID_CONTR_SEQ more may be that there is the record or not .....

The trigger compiles the problem occurs when I try Insert any registry on the poc_tagvalue table of the ÁCIMA quoted error.

If someone knew how to help me I'm grateful guys ....

[fsitja]

As Query has no joins and she is returning more than one line, it means that your research is being made with a set of columns that does not have Primary Key.
Look at your Poc_controle table: What is Primary Key? She has any Unique Key besides PK, where can you search for key?
From this answer changes the search to pick up the PK or a UK and the error will not happen.

The "WHERE" criteria below returns more than one line. The only reason you can find out, or who has modeled this table. Or Query is wrong or the table is modeling failure, allowing inconsistent data.

Select all

 
DT_FINAL IS NULL AND 
          FL_TIPO = 'P' AND 
          NM_TAG  = :new.NM_TAG AND 
          ID_EQUIP = :new.ID_EQUIP AND 
          ID_CENTR = :new.ID_CENTR AND 
          ID_GRUPO = :new.ID_GRUPO; 

[fsitja]

Sorry, your answer was at the same time as mine. : LOL:

The problem that the error is giving was what we explain already, but it is not the only one, as you have already discovered. If the line does not exist you will receive an error or-01403 (on Data Found), and you need to treat it in an Exception Handler at the end of the block, the way your order requirement.

[danilosantos]

So I even tried to put an exception in trigger plus the problem is that if there is no line I want to insert a new in the other table understood? I tried to do this in the trigger and did not work ... could you tell me how to make it work with Exception?

Thanks for the help .....

[fsitja]

Being very practical now, try the following (I did not test, then take a look at a good pinch of salt, it's just an idea):

Select all

 
create or replace 
TRIGGER TRG_TAGVALUE_CONTROLE_INS 
AFTER INSERT 
ON POC_TAGVALUE 
FOR EACH ROW 
DECLARE 
ID_CONTR_SEQ NUMBER; 
BEGIN 
 IF :new.NM_COLUN = 'NM_TAGPA' THEN 
      
 -- PEGAR NUMERO SEQUENCE 
 SELECT SEQ_ID_CONTR.NEXTVAL INTO ID_CONTR_SEQ FROM dual 
  WHERE EXISTS (SELECT 1 
                  FROM POC_CONTROLE 
                 WHERE DT_FINAL IS NULL AND 
                       FL_TIPO = 'P' AND 
                       NM_TAG  = :new.NM_TAG AND 
                       ID_EQUIP = :new.ID_EQUIP AND 
                       ID_CENTR = :new.ID_CENTR AND 
                       ID_GRUPO = :new.ID_GRUPO); 
  IF (ID_CONTR_SEQ IS NULL AND :new.VL_TAG = 0) THEN 
   
  -- INSERT AQUI 

[danilosantos]

Thanks for now by helping man ....

in the POC_controle table The PK is id_contr

is precisely this value that I check in the IF below

Select all

 
SELECT ID_CONTR INTO ID_CONTR_SEQ  
    FROM POC_CONTROLE  
    WHERE DT_FINAL IS NULL AND  
          FL_TIPO = 'P' AND  
          NM_TAG  = :new.NM_TAG AND  
          ID_EQUIP = :new.ID_EQUIP AND  
          ID_CENTR = :new.ID_CENTR AND  
          ID_GRUPO = :new.ID_GRUPO;  
 
IF (ID_CONTR_SEQ IS NULL AND :new.VL_TAG = 0) THEN 

If it is null I want to insert a new line in the table poc_controle

otherwise I want Just update the value in the POC_controle table with the PK - ID_CONTR in the command below

Select all

 
      -- ATUALIZA TABELA CONTROLE  
      UPDATE POC_CONTROLE SET DT_FINAL = :new.DT_TAG_VALUE  
      WHERE ID_CONTR = ID_CONTR_SEQ; 

understood what I want to do?

Thanks ....

[fsitja]

So when you run that select to get PK, it will return you more than one line. It will not come just a value of id_contr: will return you a set of lines that satisfy your search criterion (your WHERE). This will cause the exception that you are getting, you can not use a SELECT INTO.

Do not do this by testing "Flag" in an IF, it is a bad, inelegant practice. The business will be to change "SELECT INTO" to a loop cursor and tinker throughout the line set whose id_contr is returned (cursor loop itself is a bad practice, but this is for another day). Remember that as your select is not by PK, it will return multiple rows, then or you will insert a line or will give update of multiple lines .

[danilosantos]

So man .... I managed to solve the problem, thanks for help.