Solved

Differences between cursor and refcursor

Posted on 2003-11-24
3
1,251 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:king0452
3 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 250 total points
ID: 9809814
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
run sql script from putty 4 160
create a nested synonym 4 40
Oracle encryption 12 59
How to get the current date and Time upon oracle insert into a database table 9 59
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question