PL-SQL Cursors v. Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL-SQL Cursors v. Tables

  1. #1
    Join Date
    May 2001
    Posts
    4
    What is the difference between PL-SQL Cursors and Tables? I'd like to use a PL-SQL procedure that returns the result from a couple of queries. However, it is not working w/ using an out variable of ref cursor type.

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137

    Example

    declare
    type cv is ref cursor;
    outer_curs cv;
    tab_name varchar2(50);
    procedure open_cursor (curs_text in varchar2, inner_curs in out cv) is
    begin
    open inner_curs for curs_text;
    end;
    begin
    open_cursor('select table_name from user_tables', outer_curs);
    loop
    fetch outer_curs into tab_name;
    if outer_curs%notfound then
    exit;
    end if;
    dbms_output.put_line(tab_name);
    end loop;
    close outer_curs;
    end;

  3. #3
    Join Date
    May 2001
    Posts
    4
    If your selecting a table w/ your SQL statement, doesn't tab_name have to be of table type? It seems all your doing is just printing out table names. I wanted to return the result from a query, not just use a cursor to iteratively loop through each row, and print it to server output.

    Thanks though.

  4. #4
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    OK, you can have tab_name as user_tables.table_name%TYPE if you prefer. It doesn't make a lot of difference to the functioning of the procedure.

    I don't understand what you mean by "returns the result from a query". The procedure I wrote is returning a result set in the form of an open cursor, which can then be manipulated by the calling program. What exactly do you want to be able to do ?

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