PLSQL resume error while retrieving data from Database

Question

I try to extract data from the database and insert it into a record and then show it in the cmd, But when I get a higher registration number the procedure requires an error message.

Code:

SET SERVEROUTPUT ON
DECLARE
  TYPE trec IS RECORD ( 
    cd_multi_empresa NUMBER(8,2),
    tp_atendimento   CHAR(2)
  );

vcAtendimento trec;
cAtendimento VARCHAR2(1000) := 'select cd_multi_empresa,tp_atendimento from cli.atendime FETCH FIRST 2 ROWS ONLY';

BEGIN
  Dbms_Output.Put_Line('inicio');
    EXECUTE IMMEDIATE cAtendimento INTO vcAtendimento; 
    Dbms_Output.Put_Line(vcAtendimento.tp_atendimento);
  Dbms_Output.Put_Line('fim');
END;
/

Error message:

Bug report - ORA-01422: exact extraction returns more than the requested number of lines ORA-06512: in line 12 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 start

Answer 1

This error happens because your query returns more than one record, and the record only supports 1.

To return several you need to work with collections, making a table of record and filling with the Bulk Collect command

ex:

create table Teste1 (c1 number, c2 number);
insert into teste1 values (1, 1);
insert into teste1 values (2, 2);
-----------------

declare
  type TTeste is record(
    campo1 number,
    campo2 number);

  type TTesteTAB is table of TTeste;
  vTeste TTesteTAB;

  ConsultaDinamica varchar(200) := 'select * from teste1';
begin
  execute immediate ConsultaDinamica Bulk Collect
    into vTeste;
  dbms_output.put_line('Quantidade de registros: ' || vteste.count);
  dbms_output.put_line('Registro 1, campo1: ' || vteste(1).campo1);
  dbms_output.put_line('Registro 2, campo1: ' || vteste(2).campo1);
end;