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

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
danilosantos
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Mon, 07 Sep 2009 12:15 am
Location: São Paulo - SP
Contact:

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
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Mon, 07 Sep 2009 12:15 am
Location: São Paulo - SP
Contact:

More like I do to solve this problem?
SergioLBJr
Rank: Oracle Guru
Rank: Oracle Guru
Posts: 448
Joined: Tue, 16 Jun 2009 3:07 pm
Location: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Mon, 07 Sep 2009 12:15 am
Location: São Paulo - SP
Contact:

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 ....
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

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; 
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Mon, 07 Sep 2009 12:15 am
Location: São Paulo - SP
Contact:

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 .....
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Mon, 07 Sep 2009 12:15 am
Location: São Paulo - SP
Contact:

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 ....
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Mon, 07 Sep 2009 12:15 am
Location: São Paulo - SP
Contact:

So man .... I managed to solve the problem, thanks for help.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest