return mulitple rows

[mooks]

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!

[jmodic]

ref cursor .....

http://osi.oracle.com/~tkyte/ResultSets/index.html

[mooks]

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!

[pando]

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]

[mooks]

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...

[ales]

Add one more END; for the end of the package body.
No RETURN since RETURN_CURSOR is a procedure, not a function.

[mooks]

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!

[ales]

...

  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

[ales]

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

[mooks]

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]