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!

Fetching output from sys_refcursor../

Discussion in 'SQL PL/SQL' started by Vicky, Oct 19, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to fetch output from a procedure using sys_refcursor.?

    The refcursor procedure is as below:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE p_refcursor(i IN NUMBER,o OUT sys_refcursor)
    AS
    BEGIN
    OPEN o FOR
    SELECT * FROM employees
    WHERE emp_id=i;
    END;
    And the plsql code which i tried to fetch is as below:

    Code (SQL):
    DECLARE
    i NUMBER:=50;
    o sys_refcursor;
    BEGIN
    p_refcursor(i,o);
    dbms_output.put_line('o+++'||o);
    END;
    While executing it, i'm getting the error as below..

    Error report -
    ORA-06550: line 6, column 22:
    PLS-00306: wrong number or types of arguments in call to '||'
    ORA-06550: line 6, column 1:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    Cud U tel me how to fetch result from this procedure.,?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    that you wanted to make?

    "While executing it, i'm getting the error as below.." - you need to specify all fields of record


    for example

    Code (SQL):
    DECLARE

       l_cur    sys_refcursor;

       l_tmp all_tables%rowtype;

       PROCEDURE test_dc (i all_tables.table_name%TYPE ,c OUT sys_refcursor)
       AS
       BEGIN
           OPEN c FOR  SELECT * FROM all_tables  WHERE TABLE_NAME  = i;
       END;

    BEGIN
        test_dc('DUAL',l_cur);
        loop
            fetch l_cur INTO l_tmp ;
            exit WHEN l_cur%notfound;
            dbms_output.put_line(l_tmp.TABLE_NAME || ' size of ' ||l_tmp.blocks||' blocks');
        END loop;
        close l_cur;
    END;      
     
    Last edited: Oct 19, 2015
  3. koti

    koti Newly Initiated

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    20
    Location:
    pune
    Hi Vicky,

    plz check below example i am using emp table.i hope understand you.

    create or replace procedure all_my(dept_no number, c1 out sys_refcursor)
    is

    i emp%rowtype;

    begin

    open c1 for select * from emp where deptno=dept_no;

    loop

    fetch c1 into i;

    exit when c1%notfound;

    dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal||' '||i.deptno);

    end loop;

    close c1;

    end;

    upload_2015-12-11_8-43-30.png
     
    Vicky likes this.