Working with REF Cursors – With Examples

A ref cursor is a variable, defined as a cursor type, which points to, or references a cursor result. A cursor variable which is based on REF CURSOR data type can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Examples:

REF CURSOR WITH %TYPE:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  en employee.ename%TYPE;
BEGIN
  OPEN c_employee FOR select ename from employee;
  LOOP
      FETCH c_employee INTO en;
      EXIT WHEN c_employee%NOTFOUND;
      dbms_output.put_line(en);
  END LOOP;
  CLOSE c_employee;
END;

REF CURSOR WITH %ROWTYPE:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  er employee%ROWTYPE;
BEGIN
  OPEN c_employee FOR select * from employee;
  LOOP
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.ename || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
END;

REF CURSOR WITH RECORD TYPE:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  TYPE rec_emp IS RECORD
  (
    name       VARCHAR2(40),
    position   VARCHAR2(40)
  );
  er rec_emp;
BEGIN
  OPEN c_employee FOR select ename,position from employee;
  LOOP
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
END;

REF CURSOR WITH MULTIPLE QUERIES:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_employee r_cursor;
  TYPE rec_emp IS RECORD
  (
    name       VARCHAR2(40),
    position   VARCHAR2(40)
  );
  er rec_emp;
BEGIN
  OPEN c_employee FOR select ename,position from employee where deptname = ‘IT’;
  dbms_output.put_line('Department: IT');
  dbms_output.put_line('--------------');
  LOOP
      FETCH c_employee INTO er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
  OPEN c_employee FOR select ename,position from employee where deptname = ‘Finance’;
  dbms_output.put_line('Department: Finance');
  dbms_output.put_line('--------------');
  loop
      fetch c_employee into er;
      exit when c_employee%NOTFOUND;
      dbms_output.put_line(er.name || ' - ' || er.position);
  END LOOP;
  CLOSE c_employee;
END;

Normal Cursor Vs. Ref Cursor:

Normal Cursor	REF Cursor
It is static in definition.	It may be dynamically opened or opened based on logic.
It can’t be returned to a client.	It can be returned to a client.
A normal cursor can be global.	A ref cursor cannot. (you cannot define them OUTSIDE of a procedure / function)
A normal cursor can’t be passed from subroutine to subroutine.	A ref cursor can be passed from subroutine to subroutine.

The performance of normal cursors is much more efficient than ref cursors and hence you should use ref cursor only when you absolutely have to.

Reference:

http://asktom.oracle.com

http://www.devshed.com

OAF MVC Architecture

OAF is a java based application framework to develop web based applications that link to Oracle Applications instance while maintaining all the security features of that apps instance. A framework is a specialized set of related classes designed to make application development easier.  In effect, a framework implements part of an application so developers don’t have to write all of its code from scratch; they can use the framework as the basis for their work and while focusing on the additional code required to implement their specific application requirements.

OA Framework follows Model, View and Controller (MVC) Architecture as described below:

1] Model

Model contains the components which handles data directly from Database. It Includes Business Components for Java (BC4J Objects) which mainly are:

Entity Objects (EO):

Entity Objects are generally based on one table which encapsulate the business rules. These objects are used by OAF page to perform update/insert/delete operations. You can join two EOs using Entity Associations.

View Objects (VO):

These objects contain a SQL query that queries the data from database and present it in the OAF page. VOs can be based on one or many EOs or a SQL query. Two VO can be linked together through a View Link.

Application Module (AM):

It is a container for related BC4J objects and provides the database connection. It also provides Transaction Context (OADBTransaction) or Transaction Management. An AM can have more nested AM contained in it along with other BC4J components. But it is mandatory to have an AM for an OAF page.

2] View

View contains the actual page items on page which user can see. The view in OAF comprises of various page level items like text fields, buttons, regions, links etc. These items are visible on any OAF page. These items can either be tied to VO attribute or having a constant value or populated at run time based through controller logic.

Please note that- View Layer is altogether different than View Object!!!!!

3] Controller

Controller handles all the user actions done on the page. OAF requires a java controller class to be defined for a page/region which handles various page level actions. The important methods in this class are:

A] ProcessRequest

     This request is called when page is rendered. Any logic to be executed during page initialization is kept here.

B] ProcessFormRequest

Any page submit action causes ProcessFormRequest to be executed. The logic put here typically is that which needs to be executed after actions like button click or any other page submit action.

The Controller class is mostly used to put logic for actions on page such as button clicks, navigation to other pages. The two objects that are passed to controller methods are OAPageContext and OAWebBean. OAPageContext provides access to objects like AM class, page parameters, session values, navigation methods. OAWebBean is generally used to get a handle of page items.

In coming posts, I will try to write more details about these components and how they works….so stay tuned!