Oracle Tips and Techniques

HTML clipboardMany of you may have heard of the terms REF CURSOR and cursor variable. This article here will explain you clearly – what is a ref cursor and cursor variable. Then it will demonstrate you how to use a ref cursor and cursor variables with example.

A REF CURSOR is a cursor which does not have a fixed SQL query associated with it. Basically you declare a TYPE of REF CURSOR type. Then you define a variable of that TYPE. That variable is called and cursor variable.

Then you assign a query to that cursor variable. After that you can use that cursor variable like any other cursor.

CURSOR VARIABLE is a variable whose data type is a TYPE of REF CURSOR.

On the other hand, in a normal cursor you write a select query and you give a name to that select query. You can open, fetch data and close that cursor whenever you want.

For example, you declare a normal cursor as:

cursor emp_cur is
select * from emp where deptno =10; Continue reading →