Differences between cursor and refcursor

Please can anybody list out the differences between the Cursor and REFCursor.

Preparing for an interview.. Please list out any other related questions for interview purpose.

Thanks in advance.
king0452Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewstCommented:
A cursor is constant: it is linked to one defined query like this:

declare
  cursor c1 (p_deptno in emp.deptno%TYPE) is select ename from emp where deptno = p_deptno;
  ...

A ref cursor is a cursor variable: the actual query can be changed at runtime like this:

procedure p( param1 in number ) is
  type rc is ref cursor;
  c1 rc;
begin
  if param1 = 1 then
    open rc for select * from emp;
  else
    open rc for select * from dept;
  end if;
  ...

Ref cursors are also used in dynamic SQL, where the query is defined as a text string, like this:

procedure p( p_sql in varchar2 ) is
  type rc is ref cursor;
  c1 rc;
begin
  open rc for p_sql;
  ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.