SYS_REFCURSOR

http://forums.oracle.com/forums/thread.jspa?threadID=329109

Hi,

I did my research in the google already and found the above link. I wanted more specific answer. Finally I posted here.

I need some more examples for each to diiferentiate between the two.

Thanks
Victor

Hi,

Can you please help in the below code to re-write without sys_refcursor? which mean by using only REFCURSOR. Then I'll understand what is the exact difference between the two!!!

CREATE OR REPLACE FUNCTION test (sal1 IN NUMBER, sal2 IN NUMBER)
  2     RETURN sys_refcursor
  3  IS
  4     out_csr   sys_refcursor;
  5  BEGIN
  6     OPEN out_csr FOR
  7       SELECT employee_id, last_name, first_name, sal, dept
  8          FROM emp where sal between sal1 and sal2;
 9     RETURN out_csr;
 10  END;
 11  /

Hi

Please validate below code with just REF CURSOR. Then when to use REF CURSOR & SYS_REFCURSOR?

CREATE OR REPLACE FUNCTION test (sal1 IN NUMBER, sal2 IN NUMBER)
  2     RETURN emp_refcursor
  3  IS
  4      type emp_refcursor IS REF CURSOR;
  5     out_csr   emp_refcursor;
  6  BEGIN
  7     OPEN out_csr FOR
  8       SELECT employee_id, last_name, first_name, sal, dept
  9          FROM emp where sal between sal1 and sal2;
 10     RETURN out_csr;
 11  END;
 12  /

A REFCURSOR is a cursor variable. It is defined with a specific structure(record type).

A SYS_REFCURSOR is a cursor variable which is defined without any specific record type (structure). It is a 'weak' declaration of refcursor. An e.g. of it's use would be : if you intend to open a cursor through dynamic query whose record structure is not known in advance.

- p

HI pal2ie:,

You said REF CURSOR is defined with a specific structure(record type). In my example above I did not specified any structure? Please can you give me little more explanations on this?

--victor

Hi pal2ie:,

I read your article above. It made me sense. But....

Still do you find any problem with my code in  ID: 25928333.

I can still use same way as below right?

OPEN out_csr FOR
       SELECT employee_id, last_name, first_name, sal, dept
          FROM emp where sal between sal1 and sal2;
close out_csr;
OPEN out_csr FOR
       SELECT employee_id, last_name
          FROM emp where sal between sal1 and sal2;
close out_csr;
OPEN out_csr FOR
       SELECT employee_id
          FROM emp where sal between sal1 and sal2;
close out_csr;

Hi jocave:,

Finally you enlightened me with the beautifully descriptive answer. But I do respect others helping me as well.

Thanks  everybody in the loop.

--Victor.

great job, excellent