Home » SQL & PL/SQL » SQL & PL/SQL » No data found
No data found [message #589405] Fri, 05 July 2013 05:16 Go to next message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
Hi,
v_empno1 emp.empno%type:=&empno;
v_ename emp.ename%type;
v_empno2 emp.empno%type:=&empno;

Here i declared 2 variables (empno1,empno2), i want to display 2 Employees name based on my 2 empno's, i entered into the empno1(7369) ,empno2(1234),
But,messages displays like that 1.....,2.Employee not found with 1234, in line one no message display, i mean empty line,why it has shows, not even shows empno1(7369) value..

Thanks,
siva
Re: No data found [message #589406 is a reply to message #589405] Fri, 05 July 2013 05:19 Go to previous messageGo to next message
Littlefoot
Messages: 19468
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How is anyone supposed to debug your code, if you don't post it?
Re: No data found [message #589407 is a reply to message #589406] Fri, 05 July 2013 05:29 Go to previous messageGo to next message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks for reply,

It displays ony empno2(1234)--Employee not found with 1234 like that,when i pass empno1(7369),empno2(1234)

DECLARE
v_employeeno emp.empno%TYPE;
v_empno1 emp.empno%TYPE := &empno1;
v_ename emp.ename%TYPE;
v_empno2 emp.empno%TYPE := &empno2;
BEGIN
v_employeeno := v_empno1;

SELECT ename
INTO v_ename
FROM emp
WHERE empno = v_empno1;

dbms_output.Put_line('First employee name:'
||v_ename);

v_employeeno := v_empno2;

SELECT ename
INTO v_ename
FROM emp
WHERE empno = v_empno2;

dbms_output.Put_line('Second employee name:'
||v_ename);
EXCEPTION
WHEN no_data_found THEN
dbms_output.Put_line('Employee not found with '
||v_employeeno);
END;
Re: No data found [message #589411 is a reply to message #589407] Fri, 05 July 2013 05:39 Go to previous messageGo to next message
Littlefoot
Messages: 19468
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is my output:
SQL> DECLARE
  2  v_employeeno emp.empno%TYPE;
  3  v_empno1 emp.empno%TYPE := &empno1;
  4  v_ename emp.ename%TYPE;
  5  v_empno2 emp.empno%TYPE := &empno2;
  6  BEGIN
  7  v_employeeno := v_empno1;
  8
  9  SELECT ename
 10  INTO v_ename
 11  FROM emp
 12  WHERE empno = v_empno1;
 13
 14  dbms_output.Put_line('First employee name:'
 15  ||v_ename);
 16
 17  v_employeeno := v_empno2;
 18
 19  SELECT ename
 20  INTO v_ename
 21  FROM emp
 22  WHERE empno = v_empno2;
 23
 24  dbms_output.Put_line('Second employee name:'
 25  ||v_ename);
 26  EXCEPTION
 27  WHEN no_data_found THEN
 28  dbms_output.Put_line('Employee not found with '
 29  ||v_employeeno);
 30  END;
 31  /
Enter value for empno1: 7369
Enter value for empno2: 1234
First employee name:SMITH                       --> SMITH is right here!
Employee not found with 1234

PL/SQL procedure successfully completed.

SQL>

What does
select * from emp
return in your schema?
Re: No data found [message #589412 is a reply to message #589411] Fri, 05 July 2013 05:43 Go to previous messageGo to next message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
Oh! sorry, in empno1(1234),empno2(7369),It displays only empno1(1234)--Employee not found with 1234 and it not showing empno2(7369) values,it shows an empty...

Thanks,
Re: No data found [message #589416 is a reply to message #589412] Fri, 05 July 2013 05:55 Go to previous messageGo to next message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
i want to show when i pass in empno1(1234),if not found then dispaly --Employee not found with 1234, if pass empno2(7369) then display 'second employee name:SMITH' like that..
Re: No data found [message #589417 is a reply to message #589416] Fri, 05 July 2013 05:55 Go to previous messageGo to next message
Littlefoot
Messages: 19468
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you review what I posted?
Re: No data found [message #589418 is a reply to message #589417] Fri, 05 July 2013 05:59 Go to previous messageGo to next message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
Yes,

It displays 14 records..
Re: No data found [message #589420 is a reply to message #589418] Fri, 05 July 2013 06:01 Go to previous messageGo to next message
Littlefoot
Messages: 19468
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Great! Then everything is OK!

I meant this:
Enter value for empno1: 7369
Enter value for empno2: 1234
First employee name:SMITH                       --> SMITH is right here!
Employee not found with 1234

PL/SQL procedure successfully completed.
Re: No data found [message #589421 is a reply to message #589420] Fri, 05 July 2013 06:07 Go to previous messageGo to next message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
Ok,

It works fine, when i pass 1234 in empno1 ,7369 in empno2, it displays only"Employee not found with 1234", and about the empno2?,it shows an empty record in databse..Please suggest me how to rectify this?

Thanks,
Re: No data found [message #589422 is a reply to message #589421] Fri, 05 July 2013 06:12 Go to previous messageGo to next message
Littlefoot
Messages: 19468
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah, now I understand! You need to enclose both SELECTs into their own BEGIN-EXCEPTION-END blocks, so that failure of one SELECT doesn't affect another.
SQL> declare
  2     v_employeeno   emp.empno%type;
  3     v_empno1       emp.empno%type := &empno1;
  4     v_ename        emp.ename%type;
  5     v_empno2       emp.empno%type := &empno2;
  6  begin
  7     begin
  8        v_employeeno := v_empno1;
  9
 10        select ename
 11          into v_ename
 12          from emp
 13         where empno = v_empno1;
 14
 15        dbms_output.put_line ('First employee name:' || v_ename);
 16     exception
 17        when no_data_found
 18        then
 19           dbms_output.put_line ('Employee not found with ' || v_employeeno);
 20     end;
 21
 22     begin
 23        v_employeeno := v_empno2;
 24
 25        select ename
 26          into v_ename
 27          from emp
 28         where empno = v_empno2;
 29
 30        dbms_output.put_line ('Second employee name:' || v_ename);
 31     exception
 32        when no_data_found
 33        then
 34           dbms_output.put_line ('Employee not found with ' || v_employeeno);
 35     end;
 36  end;
 37  /
Enter value for empno1: 1234
Enter value for empno2: 7369
Employee not found with 1234
Second employee name:SMITH

PL/SQL procedure successfully completed.

SQL>
Re: No data found [message #589423 is a reply to message #589422] Fri, 05 July 2013 06:15 Go to previous message
mist598
Messages: 897
Registered: February 2013
Location: Hyderabad
Senior Member
Yes,

Actually i wrote an exception at last to both..

Great! Thanks! Razz Smile
Previous Topic: Replace
Next Topic: ORA-31603 when using DBMS_JOB.SUBMIT
Goto Forum:
  


Current Time: Wed Aug 20 12:06:04 CDT 2014

Total time taken to generate the page: 0.08086 seconds