Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: April 19, 2017 - 6:45 am UTC

Last updated: July 03, 2018 - 12:30 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi,

When i learning about Cursor Variable (Ref Cursor) on One Website I found this Code.

CREATE OR REPLACE FUNCTION f RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR select * from dual;
  RETURN c;
END;
/

set serveroutput on
DECLARE
 c SYS_REFCURSOR;
 v VARCHAR2(1);
BEGIN
 c := f();   -- Get ref cursor from function
 LOOP 
   FETCH c into v;
   EXIT WHEN c%NOTFOUND;
   dbms_output.put_line('Value from cursor: '||v);
 END LOOP;
END;
/


Then i confused with this word "SYS_REFCURSOR". Would you tell me what is it, and also Difference between Ref Cusrosr and Sys_refCursor?

and Connor said...

There is no real difference, in fact, deep down in the data dictionary, we have this definition:

  type sys_refcursor is ref cursor;


So it just saves you from defining your own type.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 19, 2017 - 9:10 am UTC

Please Answer this 2
1.Can We Create Ref Cur Type like SYS_REFCURSOR
2.Does Ref Cursor Fetches Row one by one Like Norma Cursor or Sends all rows once at a time to Client
Connor McDonald
April 19, 2017 - 9:13 am UTC

They are one and the same

Fetching is under *your* control. You can fetch one row, or fetch many.

A reader, May 21, 2017 - 4:27 am UTC

could you show me one example on fetching one row and all rows.
Connor McDonald
May 23, 2017 - 1:30 am UTC


SQL> CREATE OR REPLACE FUNCTION f1 RETURN SYS_REFCURSOR
  2  AS
  3    c SYS_REFCURSOR;
  4  BEGIN
  5    OPEN c FOR select * from dual;
  6    RETURN c;
  7  END;
  8  /

Function created.

SQL>
SQL> set serveroutput on
SQL> DECLARE
  2   c SYS_REFCURSOR;
  3   v VARCHAR2(1);
  4  BEGIN
  5   c := f1();   -- Get ref cursor from function
  6   LOOP
  7     FETCH c into v;
  8     EXIT WHEN c%NOTFOUND;
  9     dbms_output.put_line('Value from cursor: '||v);
 10   END LOOP;
 11  END;
 12  /
Value from cursor: X

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION f2 RETURN SYS_REFCURSOR
  2  AS
  3    c SYS_REFCURSOR;
  4  BEGIN
  5    OPEN c FOR select ename from scott.emp;
  6    RETURN c;
  7  END;
  8  /

Function created.

SQL>
SQL> set serveroutput on
SQL> DECLARE
  2   c SYS_REFCURSOR;
  3   v VARCHAR2(20);
  4  BEGIN
  5   c := f2();   -- Get ref cursor from function
  6   LOOP
  7     FETCH c into v;
  8     EXIT WHEN c%NOTFOUND;
  9     dbms_output.put_line('Value from cursor: '||v);
 10   END LOOP;
 11  END;
 12  /
Value from cursor: SMITH
Value from cursor: ALLEN
Value from cursor: WARD
Value from cursor: JONES
Value from cursor: MARTIN
Value from cursor: BLAKE
Value from cursor: CLARK
Value from cursor: SCOTT
Value from cursor: KING
Value from cursor: TURNER
Value from cursor: ADAMS
Value from cursor: JAMES
Value from cursor: FORD
Value from cursor: MILLER

PL/SQL procedure successfully completed.

SQL>
SQL>


A reader, June 26, 2018 - 7:37 pm UTC

Hi everyone,

I'm having a very weird problem, I'm hoping you can point me in the right direction. I'm trying to assist a developer who is getting different results from a query vs when it is called inside a stored proc in a refcursor. When we execute the query standalone, it produces 3 rows. From the proc, none. It's exactly the same, same parameters etc.

I wish I could post the entire test case but it's a pretty long query and I'm concerned about revealing anything sensitive. The only additional minor complication is that one of the inputs is a variable in list, so those are loaded into a temp table and joined to. When executing the query standalone, I need to populate the table myself beforehand.

Here's the general structure of the procedure:

CREATE OR REPLACE PROCEDURE search (
    ntype      IN number_array,
    id         IN NUMBER,
    username   IN VARCHAR2,
    keyword    IN VARCHAR2,
    p_ref      OUT SYS_REFCURSOR
) AS
BEGIN FORALL i IN 1..ntype.count
    INSERT INTO api.file_id ( file_id ) VALUES ( ntype(i) ); 
    
    OPEN p_ref FOR
 /* Big query */;
END;
/


I am calling it like so:

  1  DECLARE
  2    NTYPE API.NUMBER_ARRAY;
  3    l_ID NUMBER;
  4    l_USERNAME VARCHAR2(200);
  5    l_KEYWORD VARCHAR2(200);
  6    --P_REF SYS_REFCURSOR;
  7  BEGIN
  8    -- Modify the code to initialize the variable
  9     NTYPE := api.number_array(12345,6789,101112,);
 12    ID := 432;
 13    USERNAME := 'jdoe';
 14    KEYWORD := '%files%';
 15    API.GROUP_DETAILS_SEARCH(
 16      NTYPE => l_NTYPE,
 17      ID => l_ID,
 18      USERNAME => l_USERNAME,
 19      KEYWORD => l_KEYWORD,
 20      P_REF => :RC
 21    );
 22* END;
 23  /

PL/SQL procedure successfully completed.

SQL> print rc

no rows selected


Connor McDonald
June 27, 2018 - 1:16 am UTC

Without a test case, its hard to determine, but my suspicion would be related to the temporary table, ie

1) make sure you are not using autonomous transactions anywhere, so that the temp table is being (inadvertently) being populated in another session and hence invisible to you

2) make sure you are not doing any commits if the temp table is defined as default (ie, delete rows on commit).

Alex A, June 27, 2018 - 3:24 pm UTC

Do you mind if I emailed the test case to you guys?
Connor McDonald
July 01, 2018 - 5:32 am UTC

Sure, flick it over to: asktom_us@oracle.com with subject: Question 9534472300346065516

============================

OK, so here's a really basic demo that shows that the 'concept' works

SQL> create global temporary table t ( x int);

Table created.

SQL> create table t1 as select rownum g from dual connect by level <= 20;

Table created.

SQL>
SQL>
SQL>
SQL> create or replace
  2  procedure P(r  out sys_refcursor, s sys.odcinumberlist) is
  3  begin
  4    forall i in 1 .. s.count
  5       insert into t values ( s(i));
  6
  7    open r for
  8      select * from t1
  9      where g in ( select x from t ) ;
 10  end;
 11  /

Procedure created.

SQL>
SQL> variable rc refcursor
SQL> exec p(:rc, sys.odcinumberlist(1,2,3));

PL/SQL procedure successfully completed.

SQL> print rc

         G
----------
         1
         2
         3


Of course that doesn't help you but the fact that we know it "works" means we can work with your demo.

So

1) After your forall, add a "select count" on that table and dbms_out it to make sure the rows are in there as expected

2) Cut your query down to its absolute smallest whilst still incorporating the temporary table, eg (I've anonymized some of it)

SELECT
                ...
            FROM
                YOUR_FILE_TABLE f
                JOIN YOUR_TEMP_TABLE ON f.file_id = afi.file_id,
                YOUR_VERSIONS_TABLE v
            WHERE
                f.deleted = 0
                ...


Make sure the ref cur prints. If it does, slowly introduce more parts of your query back into it to isolate the "breaking point"

See how you go

Alex A, July 02, 2018 - 7:18 pm UTC

Thanks, just noticed you replied here. I'll try out your suggestions.
Connor McDonald
July 03, 2018 - 12:30 am UTC

If you can get to a point where

- "it works"
- "now i add this one more bit"
- "it no longer works"

then please post it here if you get a moment. It would be useful to see

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library