PLSQL return error when retrieving Bank data

Question

PLSQL return error when retrieving Bank data

Navigation

#1 by (1 votes)

0

I try to extract data in the database and insert it into a record and then show it in cmd, However when I get a larger number of registration 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:

Error Reporting - ORA-01422: Exact Extraction Returns More Than requested number of lines ORA-06512: on 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

java oracle pl-sql

asked by anonymous 10.05.2018 / 23:11

1 answer

How to remove the Node in Arch linux / Linux Manjaro doubts about target="_ blank"

score 1 · Accepted Answer

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;