1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get output using sys_refcursor;

Discussion in 'SQL PL/SQL' started by Vicky, Aug 7, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    While executing the block below:

    declare
    o sys_refcursor;
    begin
    open o for
    select * from hr.employees;
    end;

    I'm just getting the message below:

    anonymous block completed

    How to get the output for the above block..,?!
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Ex:

    declare
    o sys_refcursor;
    c emp%rowtype;
    begin
    open o for
    select * from emp;
    fetch o into c;
    dbms_output.put_line(c.empno||'-'||c.ename);
    close o;
    end;
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    By using the query, I'm getting only one row.,

    How can get all the rows, and Is'nt possible to get o/p without dbms, bcz in case, for a table having more than 50 columns, how can we get the O/p without mentioning column names.,,
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Use collection ....
    for example :
    Code (SQL):

    DECLARE
    cur sys_refcursor;
    TYPE tab IS TABLE OF  user_tables%rowtype;
    v_tab tab;
    BEGIN
    OPEN cur FOR SELECT * FROM user_tables ;
    loop    
        fetch cur bulk collect INTO v_tab LIMIT 100;
        exit WHEN v_tab.COUNT = 0 ;
        FOR z IN 1 .. v_tab.COUNT
        loop
            dbms_output.put_line(v_tab(z).TABLE_NAME||'-'||v_tab(z).sample_size);
        END loop;    
    END loop;
    close cur;
    END;
    /
     
    Additional links : http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php
    Dynamic SQL
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    To get all rows we need loop. DBMS is used to display the out put in toad ot sql developer. If it is rdf or oracle applications it is different.


    declare
    o sys_refcursor;
    c emp%rowtype;
    begin
    open o for
    select * from emp;
    loop
    fetch o into c;
    exit when o%NOTFOUND;
    dbms_output.put_line(c.empno||'-'||c.ename);
    end loop;
    --close o;
    end;


    how can we get the O/p without mentioning column names?

    I will give answer this later.