CODEWITHSUNDEEP
oracle-databaseplsqldatabase-cursor
Paco3196
Paco3196

Reputation: 21

Printing cursor and exception values (both won't work)

I wrote a question days ago and I finally managed to find the solution, but I have one more question now. A DBMS.OUTPUT_PUT.LINE message must be shown when introducing the name of a continent and, if that name is not on the database, an error will be shown. My problem here is that the correct message appears, but the error message won't. What could it be? Thanks in advance! As I said on my last question, some names are written in Spanish since my teacher want us to, but they can be easily understood.

DECLARE
  no_existe EXCEPTION;
  variable number;
  CURSOR listado_paises is
    SELECT *
      FROM paises
     WHERE paises.cod_continente in
           (select cod_continente
              FROM continentes
             WHERE nombre = '&escribe_nombre_cont');
BEGIN
  FOR i in listado_paises 
  LOOP
    UPDATE paises
       SET visualizado = 'Si'
     WHERE cod_continente = i.cod_continente;

    SELECT count(*)
      into variable
      FROM paises
     WHERE paises.cod_continente in
           (select cod_continente
              FROM continentes
             WHERE nombre = '&escribe_nombre_cont');

    IF variable = 0 THEN RAISE no_existe; END IF;      
    DBMS_OUTPUT.PUT_LINE('Nombre pais: ' || i.nombre);
    COMMIT;
  END LOOP;
 EXCEPTION
  WHEN no_existe THEN 
       DBMS_OUTPUT.PUT_LINE('El continente no existe, por favor, revise el nombre');
END;

This code if working fine, it's just that the second message won't show up!

Upvotes: 1

Views: 209

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You aren't seeing the message because you're obtaining and checking variable within the cursor loop; but if the value doesn't exist then the cursor query finds no rows and you don't go inside loop in the first place, so you never reach the count query.

Move the check to before the loop:

...
BEGIN

    SELECT count (*) into variable
    FROM paises
    WHERE paises.cod_continente in
    (select cod_continente FROM continentes WHERE nombre = '&escribe_nombre_cont');

    IF variable = 0 THEN
        RAISE no_existe;
    END IF;

    for i in listado_paises loop
        UPDATE paises
        SET visualizado ='Si'
        WHERE cod_continente = i.cod_continente;

        DBMS_OUTPUT.PUT_LINE ('Nombre pais: ' || i.nombre);
        --COMMIT;
    END LOOP;
EXCEPTION 
    WHEN no_existe THEN 
        DBMS_OUTPUT.PUT_LINE ('El continente no existe, por favor, revise el nombre'); 
END;

Committing inside the loop looks wrong; if some other error is thrown then you'll have a mix of updated and not-updated rows. Better to treat as a single transaction and commit/rollback the whole thing.

Of course, you don't need to update each row one-by-one, you can do a single update; and don't really need PL/SQL. But presumably this approach is required by your assignment.

Upvotes: 1

Related Questions