PL/SQL Procedure error with exception

Question

I have table named "players" like this

 Name        Country
---------- ------------
Sachin       India
Ponting      Australia

I have written a PL/SQL Procedure to execute it by giving "name" as parameter. Here is the code-

CREATE OR REPLACE PROCEDURE NEW_TEST (  player IN players.name%type, place IN players.country%type ) IS
countri players.country%type;
BEGIN
SELECT country into countri from players where name = player;
END;

DECLARE
player players.name%type; 
place players.country%type;
CURSOR cu_new0 is
SELECT name, country from players where name=player; 

BEGIN
player:='Sachin' ;
FOR pl_all in cu_new0 

LOOP
NEW_TEST (player, place);
dbms_output.put_line ('The player ' || player || ' play for ' || pl_all.country);
END LOOP;

EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      dbms_output.put_line('No such player!');
   WHEN OTHERS THEN
      dbms_output.put_line('Error!');
END;

Now when I am putting player:='Sachin' it is giving output but when I am giving player:= 'Sachin1' is not showing any output and more importantly it is not even going to exception of 'NO_DATA_FoUND'. Can you please help me in this regard. Thanx

what exception ? also provide your table structure – Ravi Jul 13 '15 at 09:21 — Ravi, Jul 13 '15 at 09:21
Its already provided. Please look on the top. – Warrior92 Jul 13 '15 at 09:26 — Warrior92, Jul 13 '15 at 09:26

score 0 · Answer 1 · answered Jul 13 '15 at 09:21

0

If you code a loop, Oracle does not throw an exception if no data is returned (just as it doesn't throw a TOO_MANY_ROWS exception if more than one row is returned).

answered Jul 13 '15 at 09:21

starko

1,150
11
26

So is there no other way of doing it? Because I want the exception to come – Warrior92 Jul 13 '15 at 09:25
Try use this example (http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/Toomanyrowsexception.htm). I think it can help you. – starko Jul 13 '15 at 09:28
Hi starko! I have gone through your and found that it is not useful to me because in that no where loop is mentioned. Without loop I can get the output as well. That code is like this: DECLARE p_name players.name%type := 'Sachin'; P_country players.country%type; BEGIN SELECT name, country INTO p_name, P_country FROM players WHERE p_name = name; DBMS_OUTPUT.PUT_LINE ('Name: '|| p_name); DBMS_OUTPUT.PUT_LINE ('Country: ' || p_country); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such player!'); WHEN others THEN dbms_output.put_line('Error!'); END; – Warrior92 Jul 13 '15 at 10:35

score 0 · Answer 2 · answered Jul 13 '15 at 09:35

0

Your one of the select clause is not handling exception; you are consuming exception in that code.

Your Original Code:

BEGIN
SELECT country into countri from players where name = player;
END;

Modify with below code--

BEGIN
SELECT country into countri from players where name = player;
exception when no_data_found then 
raise_application_error(......);
END;

answered Jul 13 '15 at 09:35

Ashish Patil

4,428
1
15
36

I tried your modification of code but no success :\ – Warrior92 Jul 13 '15 at 10:47

Arkadiusz Łukasiewicz · Answer 3 · 2015-07-13T15:44:57.343

0

DECLARE
player players.name%type; 
place players.country%type;
CURSOR cu_new0 is
SELECT name, country from players where name=player; 

type l_cu_new0 is table of cu_new0%rowtype; 
v_cu_new0 l_cu_new0; 
BEGIN
player:='Sachin' ;

open cu_new0;
  fetch cu_new0 bulk collect into v_cu_new0;
close cu_new0;
if v_cu_new0.count = 0 then 
  raise NO_DATA_FOUND;
end; 

FOR i  in v_cu_new0.first .. v_cu_new0.last   
LOOP
... 
END LOOP;

EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      dbms_output.put_line('No such player!');
   WHEN OTHERS THEN
      dbms_output.put_line('Error!');
END;

edited Jul 13 '15 at 15:44

answered Jul 13 '15 at 12:30

Arkadiusz Łukasiewicz

6,241
1
11
17

Hi! I ran you query in pl/sql developer and it gave following error. 1) ORA-06550: line14, column14 : PLS-00103 Encountered the symbol "COLLECT" when expecting one of the following : . into bulk. The symbol "." was substitued for "COLLECT" to continue; 2) ORA-06550: line 18, column 4: PLS-00103 Encountered the symbol ";" when expecting one of the following: if. The "if" was substituted for ";" to continue; 3) ORA-06550: line 22, column 1:PLS-00103 Encountered the symbol "." when expecting one of the following:(begin case declare exit for goto if loop mod null pragma raise return select – Warrior92 Jul 13 '15 at 14:44
I forget about the cursor name after fetch.My code has not been tested in Oracle it is olny example. – Arkadiusz Łukasiewicz Jul 13 '15 at 15:54

ilhan kaya · Answer 4 · 2015-07-14T12:38:51.317

0

There are your cursor is NULL. Because; Temp code block :

select name ,country from players where name = :player;

Run code block :

select name ,country from players where name = 'Schin1';

Run code block return NULL and your cursor is have NULL value. Then your for loop code block is not work.

You can solve this problem such as ;

CURSOR cu_new0 is SELECT name, country from players;

Hi @Warrior92 for edit, There are maybe you can try it ;

for pl_all in cu_new0 loop
new_test(player
        ,place);     
if pl_all.name = player then
  dbms_output.put_line('The player ' || player || ' play for ' || pl_all.country);
end if;
end loop;

edited Jul 14 '15 at 12:38

answered Jul 14 '15 at 08:14

ilhan kaya

51
10

@ ihan kaya Hi Kaya! your insights were really helpful. Now when I m putting 'Sachin1', it i showing an exception. Both on the negative side it takes the player value as Sachin by default like when i put player:='Sachin'. It showed o/p as The player Sachin play for India The player Sachin play for Australia. So it doesnt care where other players are. It just overwrites the Sachin for all the rows – Warrior92 Jul 14 '15 at 08:54
Thanks a lot! I was struggling with this for so long. You made my day :). Nice approach ya!! – Warrior92 Jul 14 '15 at 12:07

score 0 · Answer 5 · answered Jul 16 '15 at 09:47

If you really want to use a cursor, the for loop and an exception, you can try the following code:

DECLARE
    -- create table type of players
    TYPE t_players_tab IS TABLE OF players%ROWTYPE;
    -- declare variable / array which will contain the result of cursor's select
    l_players_arr   t_players_tab := NEW t_players_tab();

    CURSOR c_fetch_player IS
    SELECT
        *
    FROM
        players
    WHERE
        name = player;

    -- declare exception which is to be caught within the EXCEPTION block
    EXCEPTION e_player_not_found;
    -- init the exception giving it the sqlcode -20001 (valid numbers for custom exceptions are in range from -20000 to -20999)
    PRAGMA EXCEPTION_INIT(e_player_not_found, -20001);

BEGIN
    -- fetch the cursor result into the array
    OPEN c_fetch_player;
    FETCH c_fetch_player BULK COLLECT INTO l_players_arr;
    CLOSE c_fetch_player;

    -- check if the array contains any results
    IF l_players_arr.COUNT > 0 THEN
        -- iterate through the rows in the array
        FOR idx l_players_arr.FIRST .. l_players_arr.LAST
        LOOP
            dbms_output.put_line ('The player ' || player || ' play for ' || l_players_arr(idx).country);
        END LOOP;
    ELSE -- if the array has no rows, raise application arror with the same sqlcode as defined in EXCEPTION_INIT
        raise_application_error(-20001,'Player ' || player || 'not found');
    END IF;

    EXCEPTION
        -- catch the exception
        WHEN e_player_not_found THEN
            dbms_output.put_line(sqlcode || ': ' || sqlerrm);
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);          
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;
/

PL/SQL Procedure error with exception

5 Answers5