Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL problem
PL/SQL problem [message #204119] Fri, 17 November 2006 23:33 Go to next message
powder
Messages: 7
Registered: November 2006
Junior Member
I am trying to obtain the following output but received error messages:

Student ID: SM100
Student Name: Mike Smith
Location ID: 2
Room: 2002

SQL> DECLARE
2 v_s_id student.s_id%TYPE := 'SM100';
3 v_s_last student.s_last%TYPE;
4 v_s_first student.s_first%TYPE;
5 v_loc_id location.loc_id%TYPE;
6 v_room location.room%TYPE;
7 BEGIN
8 SELECT s_id, s_last, s_first, loc_id, room INTO v_s_id, v_s_last, v_s_first, v_loc_id, v_room
9 FROM student, location
10 WHERE s_id = v_s_id;
11 DBMS_OUTPUT.PUT_LINE('Student ID: '|| v_s_id );
12 DBMS_OUTPUT.PUT_LINE('Student Name: '|| v_s_first ||' '|| v_s_last );
13 DBMS_OUTPUT.PUT_LINE('Location ID: '|| v_loc_id );
14 DBMS_OUTPUT.PUT_LINE('Room: '|| v_room );
15 END;
16 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8

Appreciate any advise. Thanks!
Re: PL/SQL problem [message #204120 is a reply to message #204119] Fri, 17 November 2006 23:38 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

SELECT s_id, s_last, s_first, loc_id, room INTO v_s_id, v_s_last, v_s_first, v_loc_id, v_room
FROM student, location
WHERE s_id = v_s_id;


your select statement returns more than one row which is an error.
so modify your select statement to return only one row.

for example

SELECT s_id, s_last, s_first, loc_id, room INTO v_s_id, v_s_last, v_s_first, v_loc_id, v_room
FROM student, location WHERE s_id = v_s_id and s_id = <supply a unique id here>
Re: PL/SQL problem [message #204122 is a reply to message #204119] Fri, 17 November 2006 23:45 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi

as per suggested ramesh ur statement more than one row then you can use "loop" statements.


 begin
 for rec in ( select empno, ename, job, deptno from emp where empno = '&empno')
loop
 dbms_output.put_line ( 'Emp_no     :    '||rec.empno);
 dbms_output.put_line ( 'Emp_name   :    '||rec.ename);
 dbms_output.put_line ( 'Emp_job    :    '||rec.job);
 dbms_output.put_line ( 'Emp_deptno :    '||rec.deptno);
 end loop;
 end;
 /

SQL> set serveroutput on
SQL> set verify off
SQL> /
Enter value for empno: 7934
Emp_no     :    7934
Emp_name   :    MILLER
Emp_job    :    CLERK
Emp_deptno :    10

***********

  1  begin
  2   for rec in ( select empno, ename, job, deptno from emp where deptno = '&de
ptno')
  3  loop
  4   dbms_output.put_line ( 'Emp_no     :    '||rec.empno);
  5   dbms_output.put_line ( 'Emp_name   :    '||rec.ename);
  6   dbms_output.put_line ( 'Emp_job    :    '||rec.job);
  7   dbms_output.put_line ( 'Emp_deptno :    '||rec.deptno);
  8   end loop;
  9*  end;
SQL> /
Enter value for deptno: 30
Emp_no     :    7499
Emp_name   :    ALLEN
Emp_job    :    SALESMAN
Emp_deptno :    30
Emp_no     :    7521
Emp_name   :    WARD
Emp_job    :    SALESMAN
Emp_deptno :    30
Emp_no     :    7654
Emp_name   :    MARTIN
Emp_job    :    SALESMAN
Emp_deptno :    30
Emp_no     :    7698
Emp_name   :    BLAKE
Emp_job    :    MANAGER
Emp_deptno :    30
Emp_no     :    7844
Emp_name   :    TURNER
Emp_job    :    SALESMAN
Emp_deptno :    30
Emp_no     :    7900
Emp_name   :    JAMES
Emp_job    :    CLERK
Emp_deptno :    30

PL/SQL procedure successfully completed.




hope this helps
Mohammad Taj.
Re: PL/SQL problem [message #204123 is a reply to message #204120] Fri, 17 November 2006 23:49 Go to previous messageGo to next message
powder
Messages: 7
Registered: November 2006
Junior Member
I changed it to the following:

SELECT s_id, s_last, s_first, loc_id, room INTO v_s_id, v_s_last, v_s_first, v_loc_id, v_room
FROM student, location
WHERE s_id = v_s_id AND s_id = v_loc_id;

and received this error message:

DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 8
Re: PL/SQL problem [message #204140 is a reply to message #204119] Sat, 18 November 2006 01:44 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

sorry i have not seen properly your query

Actual problem is you are not using a joining condition which results a product. so use a proper joining condition then in the where clause use s_id to get only one row.

I do not know your column names of both student and location
but there should a common column in both the tables.
as per your data it may be loc_id

so assuming that loc_id is command for both the table i am rewriting your query as follows

SELECT s_id, s_last, s_first, l.loc_id, l.room INTO v_s_id, v_s_last, v_s_first, v_loc_id, v_room
FROM student s, location l
WHERE s.loc_id = l.loc_id
and s_id = v_s_id;
Re: PL/SQL problem [message #204168 is a reply to message #204140] Sat, 18 November 2006 09:02 Go to previous message
powder
Messages: 7
Registered: November 2006
Junior Member
Thank you very much!!
Previous Topic: How to change System Date
Next Topic: unable to get highest no. of emp
Goto Forum:
  


Current Time: Fri Dec 02 23:00:56 CST 2016

Total time taken to generate the page: 0.05521 seconds