Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    10

    Unanswered: Pl/SQL Beginner Problem, Selecting all records??

    SET SERVEROUTPUT ON;


    DECLARE
    student_rec student%ROWTYPE;
    BEGIN
    SELECT *
    INTO student_rec
    FROM student
    WHERE student_id = 156 ;
    DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
    'First Name : '|| student_rec.first_name || chr(10)||
    'Phone Number : '|| student_rec.phone || chr(10)||
    'Reg Date : '|| student_rec.registration_date);
    EXCEPTION
    WHEN no_data_found THEN
    RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
    WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
    END;
    .

    In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one???

    Any help appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    33
    just remove the condition tht where student id = 156:

    SELECT *
    INTO student_rec
    FROM student

    above script will select all the student in student and store it into the sudent_rec .

    RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');

    when you want to raise the exeception just write it ther is no record in the table instad of student with id= 156 is not in the database.

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: Pl/SQL Beginner Problem, Selecting all records??

    Hi,

    Use a cursor & remove the Hard coded value 156 in the query.

    Originally posted by iknownothing
    SET SERVEROUTPUT ON;


    DECLARE
    student_rec student%ROWTYPE;
    BEGIN
    SELECT *
    INTO student_rec
    FROM student
    WHERE student_id = 156 ;
    DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
    'First Name : '|| student_rec.first_name || chr(10)||
    'Phone Number : '|| student_rec.phone || chr(10)||
    'Reg Date : '|| student_rec.registration_date);
    EXCEPTION
    WHEN no_data_found THEN
    RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
    WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
    END;
    .

    In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one???

    Any help appreciated.
    SATHISH .

  4. #4
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    10
    When I remove the WHERE statement, I get an error when I run it saying
    "-1422 ORA-01422: exact fetch returns more than requested number of rows"

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Use CURSOR to avoid this error. Because you can fetch only one row into the record type student_rec . When the value 156 is harcoded in the query , exactly one row is fetched into student_rec . So it works fine. But once you remove the hard coded value 156 from the query, all the records are fetched . Since the record type student_rec can accept only one value, it displays the error ORA-01422: exact fetch returns more than requested number of rows. To avoid this error & fetch all the records into student_rec, use a CURSOR.



    Originally posted by iknownothing
    When I remove the WHERE statement, I get an error when I run it saying
    "-1422 ORA-01422: exact fetch returns more than requested number of rows"
    SATHISH .

  6. #6
    Join Date
    Nov 2003
    Location
    Ireland
    Posts
    10
    Its ok, fixed it! Thanks.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    As an aside from your question, you should get out of the habit of doing this:

    EXCEPTION
    WHEN no_data_found THEN
    RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
    WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));

    END;

    All that does is potentially hide errors from the user and allow inconsistent partial transactions to be committed. It should be simply:

    EXCEPTION
    WHEN no_data_found THEN
    RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
    END;

Posting Permissions

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