Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    16

    Unanswered: how handle this error

    hi guys,
    watch the following code:

    skarri>select * from all_info;

    SID SMID MID NAME1 NAME
    ---------- ---------- ---------- -------------------- --------------------
    1 11 111 somu sekhar
    2 22 222 venkat ravi
    3 33 333 prasad lokam

    1 create or replace procedure apper(vname varchar2)
    2 as
    3 vid number:=null;
    4 begin
    5 select smid into vid from all_info where name1= vname;
    6 if vid=null
    7 then
    8 dbms_output.put_line('no value');
    9 else
    10 dbms_output.put_line(vid);
    11 end if;
    12* end;
    skarri>/

    Procedure created.

    skarri>exec apper('venkat');
    22

    PL/SQL procedure successfully completed.

    skarri>exec apper('out');
    BEGIN apper('out'); END;

    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "SCOTT.APPER", line 5
    ORA-06512: at line 1
    The problem here is that i intentionally gave a wrong input second time
    my thought was that as there would be on selection into vid it would take null
    and proceed with the if statement but it did not happen so instead it raised an errors as shown above
    Now can any one tell how to handle this error and proceed with the futher statements?
    Last edited by skarri; 11-13-08 at 01:55.
    "TALK SENSE TO FOOL. HE CALLS U FOOLISH"

  2. #2
    Join Date
    Sep 2008
    Posts
    16
    guy,
    with some more work out i found the solution
    here it is
    Code:
    Wrote file afiedt.buf
    
      1      create or replace procedure apper(vname varchar2)
      2      as
      3      vid number:=null;
      4      begin
      5      select smid into vid from all_info where name1= vname;
      6  dbms_output.put_line(vid);
      7  exception
      8    when no_data_found
      9     then
     10      dbms_output.put_line('no value');
     11* end;
     12  /
    
    Procedure created
    skarri>exec apper('venkat');
    22
    
    PL/SQL procedure successfully completed.
    
    skarri>exec apper('paper');
    no value
    
    PL/SQL procedure successfully completed.
    "TALK SENSE TO FOOL. HE CALLS U FOOLISH"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •