Returning multiple rows without REF CURSOR

[waitecj]

Hi guys

OS - Solaris 8
DB - 9.2.0.7

Can anyone tell me how I can return multiple rows from a call to a procedure WITHOUT using a REF CURSOR? The reason being is the application server adapter (WebSphere) that we are using can't handle the data unless the datatypes are explicitly defined.

I'll show you an example of what i'm trying to do. The query will return two rows:

SQL> SELECT r.reviewer_name, r.review_text
  2      FROM books b, reviewer r
  3      WHERE b.book_id = r.book_id
  4     AND author='WAITE';

REVIEWER_NAME
-------------------------
REVIEW_TEXT
--------------------------------------------------------

Mrs Woman
Its Brilliant.

Mr Man
Its really Brilliant.

This is the stored procedure that has explicit OUT parameters, rather than a REF CURSOR:

CREATE OR REPLACE PROCEDURE author_reviews (	auth IN books.author%TYPE, 
						rev_name OUT reviewer.reviewer_name%TYPE, 
						rev_text OUT reviewer.review_text%TYPE ) 
IS

  CURSOR cur_revs IS
    SELECT r.reviewer_name, r.review_text
    FROM books b, reviewer r
    WHERE b.book_id = r.book_id 
	AND author='WAITE';

BEGIN
  OPEN cur_revs;
  LOOP
    FETCH cur_revs INTO rev_name, rev_text;
  EXIT WHEN cur_revs%NOTFOUND;

  END LOOP;
CLOSE CUR_REVS;   
END;
/

And this is the call to the procedure:

declare

auth books.author%TYPE;
rev_name reviewer.reviewer_name%TYPE;
rev_text reviewer.review_text%TYPE;

begin


author_reviews('WAITE', rev_name, rev_text);

dbms_output.put_line('Reviews for '|| auth || ' ... reviewer ' || rev_name || ' said: '||rev_text);



end;
/

I need that call to the stored procedure to return all rows that match the query, but it is only returning one (presumably because of the scalar types I am declaring).

How would I re-write that stored procedure to return all rows?

[tabreaz]

Try out this!

CREATE OR REPLACE TYPE typ_reviews as object(
	reviewer_name	varchar2(30),
	reviewer_text	varchar2(2000)
)
/

CREATE OR REPLACE TYPE tab_reviews is table of typ_reviews
/

CREATE OR REPLACE FUNCTION author_reviews (	
	auth IN books.author%TYPE
)
RETURN tab_reviews
IS
	l_reviews tab_reviews;
BEGIN
	SELECT typ_reviews(r.reviewer_name, r.review_text)
	into l_reviews
	FROM books b, reviewer r
    	WHERE b.book_id = r.book_id 
		AND author='WAITE';

	return l_reviews;
END;

SELECT * FROM TABLE(author_reviews('WAITE'));

[waitecj]

Excelent, that may be what I was after. I'll give it a try when I get into the office.

Thanks for your time and efforts.