Sep 23, 2015

What is difference between Weak reference type and Strong reference type - PL/SQL cursor

Reference type is one of the PL/SQL Data type. PL/SQL reference type can be of Explicit/implicit type and cursor variable. The main agenda of this post is to discuss cursor type - Weak and Strong type.
The fundamental difference between weak and strong reference type is decided by RETURN clause of cursor type declaration. If RETURN type clause is missing from cursor type declaration then reference type is weak else Strong. Below is the syntax of cursor variable declaration and note that Return type is optional and it decides whether cursor type is Strong or weak.
TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];

Now,consider following concrete cursor type declarations and understand the differences between them, first strong type followed by weak type:-

--Strong cursor type, bound to EMPLOYEE rows  
TYPE company_curtype IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;

In above declaration returned type is specified as EMPLOYEES%ROWTYPE, it means the cursor variable created of this type will always be associated with EMPLOYEES table rows. such cursor type is termed  as strong declaration.

--Weak cursor type , no return type  
TYPE generic_curtype IS REF CURSOR;

In above declaration returned type is missing, it means the cursor variable created of this type will can refer to any table rows. such cursor type is termed  as weak declaration.

Advantage of Weak cursor type over Strong type: Weak cursor type is more flexible than the strong type because they can be used with any query and with any ROWTYPE structure. But it does not provide compile time verification whether correct type of record has been used for fetch cursor or not. PL/SQL runtime engine will raise the predefined ROWTYPE_MISMATCH exception at runtime ,if the query and the INTO clause do not structurally match.

Advantage of Strong cursor type over weak type: When we declare Strong cursor type, we get advantage of compile time verification by the compiler whether we have properly matched up the cursor variable's FETCH statements with its cursor object's query list/correct record type.In other words, strong cursor type provides type safety and avoid run time failure.

Consider following stored procedure and try to compile this in following scenario:-
PROCEDURE VERIFY_STRONG_CURSOR_TYPE (pet_in IN VARCHAR2)
IS
   /* Declare record to receive row fetched from cursor */
    v_emp_rec  EMPLOYEES%ROWTYPE;
    v_dept_rec DEPARTMENTS%ROWTYPE;
    v_deptId_in NUMBER(2) := 90;
    /*Strong ref cursor*/
    type employee_refcursor is ref cursor RETURN EMPLOYEES%ROWTYPE;
    /*Weak ref cursor*/   
    --type employee_refcursor_weak is ref cursor;
    v_cur employee_refcursor;
BEGIN
    -- call procedure get_employees_name and open cursor v_cur for employee records
    get_employees_name(v_deptId_in,v_cur); --v_cur referring employee records.
    dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
    dbms_output.put_line('---------------------------------');
    LOOP
        FETCH v_cur INTO v_emp_rec;
        /*uncomment below FETCH and comment above one,compile time error occuurs.*/
        --FETCH v_cur INTO v_dept_rec; 
          EXIT WHEN v_cur%NOTFOUND;
          dbms_output.put_line(v_emp_rec.FIRST_NAME|| '  ' || v_emp_rec.LAST_NAME);
      END LOOP;
      CLOSE v_cur;
END;

1. No change required, just compile it:-
 It will compile successfully and it will give proper output when we execute it, because we have created strong cursor type and using correct record type to fetch records from cursor.
2. Comment current FETCH statement and uncomment below one:- Now, this procedure will not compile and it will throw an error "PLS-00394: wrong number of values in the INTO list of a FETCH statement", because cursor variable is referring employee records(Strong type cursor) and we are trying to fetch into department type record. Strong cursor type is providing compile time safety.
3. Remove strong refcursor and uncomment weak refcursor from declaration block;- Now we will not witness any error even tough department type record is fetching from rows of employees(cursor is referring to employees record). But we will get error in run time of this procedure :"PL/SQL: Return types of Result Set variables or query do not match".Weak reference type is flexible but compile type safety is missing.

Conclusion:-  Always prefer to use strong type in procedure where record type fetching is involved, and weak type can be used where we want flexibility in handling of cursor type.
=======End of post======

Location: Hyderabad, Telangana, India