Oracle ref cursor using SQL statment

Question

What is the role of REF CURSOR in Oracle and why do we need an SQL Statement i.e.

OPEN c2 FOR sqlstmt USING c3_rec.id;

Here is the cursor code , please help .

TYPE cur_ref IS REF CURSOR;
    c2 cur_ref;
    sqlstmt VARCHAR2(2000);
    total       INTEGER;



sqlstmt:='SELECT COUNT(*) total FROM "'||owner||'".'||table||' WHERE '||:NEW.cname||' = :1';
OPEN c1 FOR sqlstmt USING :NEW.min;


BEGIN
        OPEN c2 FOR sqlstmt USING c3_rec.id;
        FETCH c2 INTO total;
        CLOSE c2;
    EXCEPTION
        WHEN others THEN
            error(1006, id: %s %s raised by: %s')

Lalit Kumar B · Accepted Answer · 2015-02-07 07:25:07Z

1

What is the role of REF CURSOR in Oracle and why do we need an SQL Statement

The basic use of REF Cursor is that, it can be opened on the server and passed to the client as a unit rather than fetching one row at a time. It can be passed as parameter to other program units.

Now coming to the SQL statement, upon execution it will fetch the required rows. The ref cursor hold the record set thus returned by the SQL statement.

So, A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.

Yes, Oracle 9i introduced the pre-defined SYS_REFCURSOR to avoid your own REF CURSOR type. However, it is a weak ref cursor type. A well defined well cursor is considered strong ref cursor type.

You will find something interesting in $ORACLE_HOME/rdbms/admin/stdspec.sql :

/* Adding a generic weak ref cursor type */
type sys_refcursor is ref cursor;

To cut it short, both are same.

So, in your case, this :

TYPE cur_ref IS REF CURSOR;
c2 cur_ref;

can be written as :

c2 SYS_REFCURSOR;

edited Feb 7, 2015 at 7:25

answered Aug 29, 2014 at 9:23

Lalit Kumar B

49.5k13 gold badges105 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3844877 Over a year ago

Thanks for this . So, does it mean that I can use dynamic SQL to convert this in SQL Server version ?

Lalit Kumar B Over a year ago

I don't know how it works in SQL Server. But whatever I demonstrated and explained holds 100% true in Oracle. You might have to do some search to find how to do it in Sql Server.

user3844877 Over a year ago

That's fine . Thanks for your help.

neshkeev · Accepted Answer · 2014-08-29 08:06:25Z

0

Using REF CURSOR you make your code more flexible, for example:

declare
  type t_c1 is ref cursor;
  c1 t_c1;
  l_count number(10);
  l_object_type varchar2(50);
begin
  l_object_type := 'TABLE';
  open c1 for 'SELECT count(*) from user_objects where object_type = :object_type' using l_object_type;
  fetch c1 into l_count;
  close c1;
  DBMS_OUTPUT.put_line('You have ' || l_count || ' tables');
  l_object_type := 'VIEW';
  open c1 for 'SELECT count(*) from user_objects where object_type = :object_type' using l_object_type;
  fetch c1 into l_count;
  DBMS_OUTPUT.put_line('You have ' || l_count || ' views');
  close c1;
  l_object_type := 'INDEX';
  open c1 for 'SELECT count(*) from user_objects where object_type = :object_type' using l_object_type;
  fetch c1 into l_count;
  close c1;
  DBMS_OUTPUT.put_line('You have ' || l_count || ' indexes');
END;

You have 32 tables
You have 0 views
You have 57 indexes

Statement processed.

If you don't want to declare your own ref cursor type you can use the pre-built SYS_REFCURSOR type.

answered Aug 29, 2014 at 8:06

neshkeev

6,4863 gold badges32 silver badges49 bronze badges

1 Comment

user3844877 Over a year ago

Thanks to both of you for your help with this .

Collectives™ on Stack Overflow

Oracle ref cursor using SQL statment

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related