Avoiding TOO_MANY_ROWS Error In Oracle

[princech]

When you use SELECT in a PL/SQL block, it's important to make sure that exactly one row will always be returned by your query. If more than one row is returned, the TOO_MANY_ROWS exception occurs. There are four ways to make sure your code is safe from this error...

Oracle's PL/SQL language has two basic mechanisms for getting data from the database: SELECT and cursors. SELECT is designed to return a single row into local variables; cursors give you the ability to select multiple rows (i.e., a "rowset") and process them one at a time.

When you use SELECT in a PL/SQL block, it's important to make sure that exactly one row will always be returned by your query. If more than one row is returned, the TOO_MANY_ROWS exception occurs. Listing A shows an example from Oracle's HR sample schema: There is more than one employee with the last name Shabbir, so the script fails.

click here to get code

[matanga]

Hola,

Por citar una profesora mia en un examen final de Analisis Matematico, "...pues, mira como no estoy de acuerdo..."

Tengo una tabla con mas de un registro.

Código:

SQL> select count(*) from t1;

  COUNT(*)
----------
     39433

Y hago un SELECT INTO de mas de un registro sin obtener la excepcion TOO_MANY_ROWS.

Código:

SQL> declare
  2  type t1_a is table of t1%rowtype index by pls_integer;
  3  t t1_a;
  4  begin
  5  select *
  6  bulk collect into t
  7  from t1;
  8  dbms_output.put_line(t.last);
  9  end;
 10  /
39433

PL/SQL procedure successfully completed.

En principio, es correcto lo posteado, solo que incompleto.

Gracias por el aporte, y recuerda que el foro es en castellano

Saludos