Exception NO_DATA_FOUND in Procedure

xiobijaph

Hi,
I want create exception 'no_data_found' in procedure but I dont know why it doesn't work:

create table player (p_name VARCHAR2(20), p_surname VARCHAR2(20));/insert into player values ('Jerry','Nowak');/CREATE PROCEDURE p_test ISnew_p_name VARCHAR2(20) :='Michael';BEGINUPDATE playerSET p_imie=new_p_name WHERE p_surname='Kowalski';EXCEPTIONWHEN no_data_found thendbms_output.put_line('bad surname');end;/SET SERVEROUTPUT ON/execute p_test;/

I did it without procedure and here is ok:

create table player (p_name VARCHAR2(20), p_surname VARCHAR2(20));/insert into player values ('Jerry','Nowak');/SET SERVEROUTPUT ON/DECLAREv_name player.p_name%TYPE;BEGINSelect p_name INTO v_name FROM player Where p_surname='Kowalski';EXCEPTIONWHEN no_data_found thendbms_output.put_line('Bad surname');end;/

L. Fernigrini

1) Try posting the code and not image, it is not possible to copy and paste from an image

2) Use:

CREATE OR REPLACE

PROCEDURE p_test

IS

     new_p_name VARCHAR2(20) :='Michael';

BEGIN

     UPDATE player

     SET p_name=new_p_name

     WHERE p_surname='Kowalski';

     IF SQL%ROWCOUNT = 0

     THEN

          RAISE no_data_found;

     END IF;

EXCEPTION

     WHEN no_data_found THEN

               dbms_output.put_line('Bad surname');

end;

/

John Thorton

xiobijaph wrote:Hi,
I want create exception 'no_data_found' in procedure but I dont know why it doesn't work:create table player (p_name VARCHAR2(20), p_surname VARCHAR2(20));/insert into player values ('Jerry','Nowak');/CREATE PROCEDURE p_test ISnew_p_name VARCHAR2(20) :='Michael';BEGINUPDATE playerSET p_imie=new_p_name WHERE p_surname='Kowalski';EXCEPTIONWHEN no_data_found thendbms_output.put_line('bad surname');end;/SET SERVEROUTPUT ON/execute p_test;/I did it without procedure and here is ok:create table player (p_name VARCHAR2(20), p_surname VARCHAR2(20));/insert into player values ('Jerry','Nowak');/SET SERVEROUTPUT ON/DECLAREv_name player.p_name%TYPE;BEGINSelect p_name INTO v_name FROM player Where p_surname='Kowalski';EXCEPTIONWHEN no_data_found thendbms_output.put_line('Bad surname');end;/

UPDATE behaves differently than SELECT & does not throw NO DATA FOUND exception

Solomon Yakobson

UPDATE (same as INSERT or DELETE) doesn't throw no data found, as John explained. You need to check SQL%ROWCOUNT which returns number of affected rows:

SQL> CREATE OR REPLACE

  2    PROCEDURE p_test

  3      IS

  4          new_p_name VARCHAR2(20) :='Michael';

  5      BEGIN

  6          UPDATE player

  7            SET p_name=new_p_name

  8            WHERE p_surname='Kowalski';

  9          IF SQL%ROWCOUNT = 0

10            THEN

11              dbms_output.put_line('bad surname');

12          END IF;

13  end;

14  /

Procedure created.

SQL> SET SERVEROUTPUT ON

SQL> execute p_test

bad surname

PL/SQL procedure successfully completed.

SQL>

SY.

xiobijaph

Ok, it works, but I need use EXCEPTION

What's commands throw DATA_NO_FOUND in procedure?

I tried use SQL%ROWCOUNT in procedure with EXCEPTION, but doesn't work

AndrewSayer

xiobijaph wrote:Ok, it works, but I need use EXCEPTIONWhat's commands throw DATA_NO_FOUND in procedure?I tried use SQL%ROWCOUNT in procedure with EXCEPTION, but doesn't work

“But doesn’t work” isn’t very helpful when we want to help you debug your code. Imagine if we just said “Do it correctly”.

The correct way to throw an error in pl/sql is with raise. You would benefit from going over the documentation if these basic things are new to you, Assuming you’re on 18c https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/index.html but they are very easy to find with a simple google search.

If If you want help debugging then you need to share your code exactly and how you are running it.

John Thorton

xiobijaph wrote:I tried use SQL%ROWCOUNT in procedure with EXCEPTION, but doesn't work

my car doesn't work

Tell me how to make my car go.

xiobijaph

If I use "SQL%ROWCOUNT = 0" then i can't use "EXCEPTION" because "dbms_output.put_line('Bad surname');" is included in IF from "SQL%ROWCOUNT"

I mean I would like use "bad surname from exception"

L. Fernigrini

1) Try posting the code and not image, it is not possible to copy and paste from an image

2) Use:

CREATE OR REPLACE

PROCEDURE p_test

IS

     new_p_name VARCHAR2(20) :='Michael';

BEGIN

     UPDATE player

     SET p_name=new_p_name

     WHERE p_surname='Kowalski';

     IF SQL%ROWCOUNT = 0

     THEN

          RAISE no_data_found;

     END IF;

EXCEPTION

     WHEN no_data_found THEN

               dbms_output.put_line('Bad surname');

end;

/

Hans Steijntjes

You can raise the exception in the IF statement as in:

IF SQL%ROWCOUNT = 0

THEN

   RAISE NO_DATA_FOUND;

END IF;

xiobijaph

This is what I want, thank you very much everyone

I was reading about RAISE but I didnt' excpect it will be good.

Solomon Yakobson

What's the point in checking condition with IF and then, when condition is met, raise exception that never happened, write exception handler for that exception instead of simply handling that condition in IF statement?

SY.