Returning multiple rows without REF CURSOR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Returning multiple rows without REF CURSOR

  1. #1
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612

    Returning multiple rows without REF CURSOR

    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:

    Code:
    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:

    Code:
    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:

    Code:
    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?
    Assistance is Futile...

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    Try out this!

    Code:
    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'));

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.
    Assistance is Futile...

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