return mulitple rows
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: return mulitple rows

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Exclamation

    Hi,

    I would like to return multiple rows through a procedure using the OUT parameter mode...however I encountered with the error ORA-01422: exact fetch returns more than requested number of rows...

    Is there any example that I can reference to return more than a row from a procedure? Thank u!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2000
    Posts
    387

    Unhappy

    Hi,

    I wrote a sample as follows but i have encountered a compilation error...

    CREATE OR REPLACE PACKAGE PACK_TEST AS
    TYPE TY_Cursor IS REF CURSOR ;
    PROCEDURE RETURN_CURSOR (p_id IN varchar2,
    Out_Cursor IN OUT TY_Cursor) ;
    END;

    create or replace package body PACK_TEST AS
    procedure RETURN_CURSOR (p_id in varchar2,
    Out_Cursor IN OUT TY_Cursor)
    as
    CURSOR c_student IS
    select .....
    from ....
    where ..... ;

    v_student
    VARCHAR2(50);
    v_count
    NUMBER(2);

    begin
    OPEN c_student;
    LOOP
    FETCH c_student into v_student;
    EXIT WHEN c_student%NOTFOUND;
    select count(*) into v_count
    from students
    where lower(student_name) like '%'||v_student||'%';

    IF v_count <> 0 THEN
    open Out_Cursor for
    select exam_id
    from student_exam
    where lower(student_name) like '%'||v_student||'%';
    return Out_Cursor;
    END IF;
    END LOOP;
    END;
    /



    The error that I got is :

    PLS-00103: Encountered the symbol "end-of-file" when expecting
    one of the following:
    begin end function package pragma procedure form

    Any help please?
    Thank you!


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    didd you run all that in a go?
    You have to create the package first then the body

    you miss a / after

    CREATE OR REPLACE PACKAGE PACK_TEST AS
    TYPE TY_Cursor IS REF CURSOR ;
    PROCEDURE RETURN_CURSOR (p_id IN varchar2,
    Out_Cursor IN OUT TY_Cursor) ;
    END;



    after creating the package you run the rest to create the body

    [Edited by pando on 09-10-2001 at 06:47 AM]

  5. #5
    Join Date
    Jan 2000
    Posts
    387

    Question

    Hi,

    Yes, I executed the package first before the package body. The package is ok, the error came from the package body. Do you have any idea what could be wrong? Thank you...

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Add one more END; for the end of the package body.
    No RETURN since RETURN_CURSOR is a procedure, not a function.

  7. #7
    Join Date
    Jan 2000
    Posts
    387

    Post

    Hi,

    I did the following modification:

    IF v_count <> 0 THEN
    open Out_Cursor for
    select exam_id
    from student_exam
    where lower(student_name) like '%'||v_student||'%';
    end;
    END IF;

    But i have encountered the following error:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    if


    Btw, how can I retrieve the data in the cursor? Thanks!

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    ...
    IF v_count <> 0 THEN
    open Out_Cursor for
    select exam_id
    from student_exam
    where lower(student_name) like '%'||v_student||'%';
    --return Out_Cursor;
    END IF;
    END LOOP;
    END RETURN_CURSOR; -- end of procedure
    END PACK_TEST; -- end of package body
    /



    ... how can I retrieve the data in the cursor?

    See the URL jmodic posted earlier, a lot of examples there!

    Ales


  9. #9
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    One more thing ...
    The procedure probably will NOT work as you expect.
    In OUT variable Out_Cursor you get the exam_id's of the last student only. All the previous are thrown away because the cursor is opened repeatedly.
    It looks you'd like to get multiple cursors, not multiple rows.

    Ales

  10. #10
    Join Date
    Jan 2000
    Posts
    387

    Post

    Hi,

    So I am in the wrong track then..... how to retrieve all the exam id for all students that I have selected???

    I am also aware that if one of the set returns 0, then it will not be able to go through the rest of the results even when I use no data found exceptions...any help pls??

    [Edited by mooks on 09-10-2001 at 11:33 PM]

Posting Permissions

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



Click Here to Expand Forum to Full Width