CODEWITHSUNDEEP
sqloracle-databasecursor
user3844877
user3844877

Reputation: 533

Oracle ref cursor using SQL statment

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')

Upvotes: 2

Views: 250

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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;

Upvotes: 1

neshkeev
neshkeev

Reputation: 6476

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.

Upvotes: 0

Related Questions