ORA-01403: no data found exception from procedure : ORA Error « System Packages « Oracle PL / SQL





ORA-01403: no data found exception from procedure

  
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> PROMPT as bind variables
as bind variables
SQL>
SQL> CREATE OR REPLACE PROCEDURE bind_test (i_emp_fname IN emp.fname%TYPE)
  2  IS
  3     v_emp_lname emp.lname%TYPE;
  4  BEGIN
  5     SELECT lname INTO v_emp_lname FROM emp WHERE fname = i_emp_fname;
  6
  7     DBMS_OUTPUT.PUT_LINE(i_emp_fname||' has a last name of '||v_emp_lname);
  8  EXCEPTION
  9     WHEN OTHERS
 10     THEN
 11        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 12  END;
 13  /

Procedure created.

SQL>
SQL>
SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> EXEC bind_test('Ron')
ORA-01403: no data found

PL/SQL procedure successfully completed.

SQL> EXEC bind_test('Mike')
ORA-01403: no data found

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

SQL>
SQL> drop table emp;

Table dropped.

SQL>

   
    
  










Related examples in the same category

1.ORA-00918: column ambiguously defined
2.ORA-00934: group function is not allowed here
3.ORA-00979: not a GROUP BY expression
4.ORA-01403: no data found
5.ORA-01422: exact fetch returns more than requested number of rows
6.ORA-01426: numeric overflow
7.ORA-01839: date not valid for month specified
8.ORA-06502: PL/SQL: numeric or value error
9.ORA-06502: PL/SQL: numeric or value error: character to number conversion error
10.ORA-06502: PL/SQL: numeric or value error: number precision too large
11.ORA-06503: PL/SQL: Function returned without value
12.ORA-14551: cannot perform a DML operation inside a query