Forum Stats

  • 3,781,278 Users
  • 2,254,498 Discussions
  • 7,879,632 Comments

Discussions

Exception NO_DATA_FOUND in Procedure

xiobijaph
xiobijaph Member Posts: 12
edited Jan 22, 2019 8:01AM in SQL & PL/SQL

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;/
xiobijaphPaulzipL. Fernigrini

Best Answer

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,696 Silver Crown
    edited Jan 21, 2019 3:01PM Accepted Answer

    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;

    /

    xiobijaph
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 21, 2019 12:54PM
    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
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited Jan 21, 2019 1:14PM

    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
    xiobijaph Member Posts: 12
    edited Jan 21, 2019 2:12PM

    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
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 21, 2019 2:21PM
    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
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 21, 2019 2:24PM
    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
    xiobijaph Member Posts: 12
    edited Jan 21, 2019 2:55PM

    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"
    pastedImage_0.png

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,696 Silver Crown
    edited Jan 21, 2019 3:01PM Accepted Answer

    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;

    /

    xiobijaph
  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited Jan 21, 2019 3:02PM

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

    IF SQL%ROWCOUNT = 0

    THEN

       RAISE NO_DATA_FOUND;

    END IF;

    xiobijaph
  • xiobijaph
    xiobijaph Member Posts: 12
    edited Jan 21, 2019 3:09PM

    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
    Solomon Yakobson Member Posts: 19,009 Red Diamond
    edited Jan 21, 2019 6:14PM

    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.

    Paulzip