PL/SQL Cursor variable - REF CURSOR

In previous post we discussed about implicit & explicit cursor and also understood how it is different from cursor variable.Lets reiterate it again, both explicit cursor and implicit cursor are associated with a specific SQL DML statement(they are tied to specific queries), whereas cursor variable can refer to multiple DML statements (off course SELECT statement) throughout session. Read The main agenda of this post is to understand the declaration and uses of cursor variable.

Cursor variable -- a reference to a cursor object or query in the database.

Cursor variable is a pointer/reference to SQL work area and its value is the address of the that work area.Since it has address of SQL work area, it can accommodate multiple select statements(as shown here) returning different result sets(rows from different table).

Syntax and life cycle of cursor variable:-

Cursor variable is of type REF CURSOR/ SYS_REFCURSOR. General syntax of cursor type & variable declaration and life cycle of cursor variable is as  is as follows: first create type of cursor then create variable of that type termed as cursor variable followed by FETCH and close.
Note:- Once cursor is closed, if try to fetch rows it will throws error ORA-01001: invalid cursor.
/* Create the cursor type.- Return type is optional */
TYPE cur_type IS REF CURSOR [RETURN table%ROWTYPE];

/* Declare a cursor variable of that type. */
cur_var cur_type;

/* Open the cursor variable, associating with it a SQL statement. */
OPEN cur_type FOR <SELECT statement>;

/* Fetch from the cursor variable. */
FETCH cur_var INTO <table_rec_type/use%ROWTYPE>;

/* Close the cursor object associated with variable. */
CLOSE cur_var;

Cursor variable and Cursor object :- 

Cursor variable dynamically binds with select statement when OPEN FOR syntax is executed
Note:- When a cursor variable is created in PL/SQL program unit, it is just a reference variable. Actual cursor object (the result set identified by the cursor SQL statement) is created when OPEN FOR is executed for select query and assigned to a cursor variable. See the following diagram to understand difference between cursor variable and cursor object:-
Cursor variable and cursor Object creation in PL/SQL- cursor variable is reference to cursor Object
Lets write a sample program to understand how do we create concrete cursor type and  its variable to fetch records:-
DECLARE 
  /*Cursor type declaration*/
  TYPE v_empcur_type IS REF CURSOR;-- RETURN EMPLOYEES%ROWTYPE;
  /*cursor variable declaration - no select statement binding here*/
  cur_var v_empcur_type;
  v_deptId   NUMBER(5) := 90;
  v_locId NUMBER(5) := 1800;
  v_emp_row EMPLOYEES%ROWTYPE;
  v_dept_row DEPARTMENTS%ROWTYPE;
BEGIN
  /*First binding:- OPEN refcursor for a select statement */
  OPEN cur_var FOR select * from employees where
                DEPARTMENT_ID = v_deptId;
  dbms_output.put_line('--First binding output--');
  dbms_output.put_line('FIRST_NAME' || '        '|| 'EMAIL');
  dbms_output.put_line('-------------     ---------------');
LOOP
    FETCH cur_var INTO v_emp_row;
      EXIT WHEN cur_var%NOTFOUND;
    dbms_output.put_line(v_emp_row.FIRST_NAME || '         
              ' || v_emp_row.EMAIL||'@devinline.com');
  END LOOP;
/*Second binding :- OPEN refcursor for another select statement */
  OPEN cur_var FOR select * from DEPARTMENTS where
                LOCATION_ID = v_locId;
  dbms_output.put_line('--Second binding output --');
  dbms_output.put_line('DEPARTMNET_NAME' || '        '|| 'MANAGER_ID');
  dbms_output.put_line('-------------     ---------------');
  LOOP
      FETCH cur_var INTO v_dept_row;
        EXIT WHEN cur_var%NOTFOUND;
      dbms_output.put_line(v_dept_row.DEPARTMENT_NAME || '         
                ' || v_dept_row.MANAGER_ID);
    END LOOP;
  CLOSE cur_var;
END;
==========Sample output========================
--First binding output--
FIRST_NAME        EMAIL
-------------     ---------------
Steven      
              SKING@devinline.com
Neena      
              NKOCHHAR@devinline.com
Lex      
              LDEHAAN@devinline.com
NIKHIL      
              NIKSR@devinline.com

--Second binding output --
DEPARTMNET_NAME        MANAGER_ID
-------------       ---------------
Marketing       201

Purchasing       114
========================================
In above program, cursor type is created followed by a cursor variable and two variable one of type employee row and another of department row type is created. In begin block, cursor is opened and associated it with a select statement. Fetch cursor in Loop and display name and email. Again same cursor is bind with another select statement and again fetch and display rows from it. Above program unit uses same cursor variable to refer two select statement one after another, it is most important and noticeable feature of cursor variable.

Question:-
Why error "ORA-01001: invalid cursor " is thrown, if try to fetch rows after cursor has been closed ?
Answer:- Always remember, cursor does nor contain value, it is just a pointer to result set. Once we execute close command, pointer to result set is removed so , cursor is just a variable now and no cursor object is attached to it, so it throws error.

Note:-

  1. Cursor variable also has same set of attributes as explicit cursor : ISOOPEN, FOUND, NOTFOUND, ROWCOUNT. Refer this table for more detail about cursor attributes.
  2. Cursor variable can be of two types: Strong type and Weak type.If RETURN clause is added while creating cursor type it is termed as Strong type, however if is missing then that type is called Weak type.Read Difference between strong type and weak type. in more detail.
  3. We can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. If either of cursor variable participating in assignment operation are weak then compile time check cannot be achieved. And if there is a type mismatch or both cursor variable are not structurally same then error is reported at runtime.
    If both cursor variable is strong type, compile time check is done and error is reported if both are structurally not same.
    --Both cursor variable are strong type,compiler time check for structure compitablity
    Type emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
    Type dept_cur_type IS REF CURSOR RETURN departments%ROWTYPE;
    emp_cur_var emp_cur_type;
    dept_cur_var dept_cur_type;
    .....
    /*compile time error for below assignment: PLS-00382: expression is of wrong type*/
    emp_cur_var := dept_cur_var;
    
    Assignment operation is allowed but cursor variable equality, inequality, or nullity check using comparison operators is not allowed. 
  4. A cursor object(a query/result-set) may be referenced by two cursor variable.
  5. NULL cannot be assigned to a cursor variable.
  6. Cursor variables cannot be declared in a package since they do not have a persistent state.
Read also:
1. Difference between strong cursor type and weak cursor type
2. Difference between SYS_REFCURSOR and REF CURSOR
3. Difference between explicit cursor and cursor variable
4. REF CURSOR, SQL and PL/SQL interaction
Previous: PL/SQL Implicit & Explicit Cursor Next: PL/SQL program unit- Procedure and Function

41 Comments

  1. I would definitely say that this blog is really useful for me and helped me to gain a clear basic knowledge on the topic. Waiting for more updates from this blog admin.

    IELTS Classes in Mumbai
    IELTS Coaching in Mumbai
    IELTS Mumbai
    IELTS Center in Mumbai
    Best IELTS Coaching in Mumbai
    Spoken English Classes in Chennai
    IELTS Coaching in Chennai
    English Speaking Classes in Mumbai

    ReplyDelete
  2. aws interview questions and answers for experienced

    AWS Interview Questions and Answers for freshers and experienced to get your dream job in AWS! 101 AWS Interview Questions for Freshers, aws interview questions and answers for experienced

    ReplyDelete
  3. Ucuz, kaliteli ve organik sosyal medya hizmetleri satın almak için Ravje Medyayı tercih edebilir ve sosyal medya hesaplarını hızla büyütebilirsin. Ravje Medya ile sosyal medya hesaplarını organik ve gerçek kişiler ile geliştirebilir, kişisel ya da ticari hesapların için Ravje Medyayı tercih edebilirsin. Ravje Medya internet sitesine giriş yapmak için hemen tıkla: https://www.ravje.com

    İnstagram takipçi satın almak için Ravje Medya hizmetlerini tercih edebilir, güvenilir ve gerçek takipçilere Ravje Medya ile ulaşabilirsin. İnstagram takipçi satın almak artık Ravje Medya ile oldukça güvenilir. Hemen instagram takipçi satın almak için Ravje Medyanın ilgili sayfasını ziyaret et: instagram takipçi satın al

    Tiktok takipçi satın al istiyorsan tercihini Ravje Medya yap! Ravje Medya uzman kadrosu ve profesyonel ekibi ile sizlere Tiktok takipçi satın alma hizmetide sunmaktadır. Tiktok takipçi satın almak için hemen tıkla: tiktok takipçi satın al

    ReplyDelete
  4. Great blog.thanks for sharing such a useful information
    Big Data Hadoop Training

    ReplyDelete
  5. This post is so interactive and informative.keep update more information...
    dot net training in anna nagar
    Dot net training in Chennai

    ReplyDelete
  6. This post is so interactive and informative.keep update more information...
    PHP Training in Anna nagar
    PHP Training in Anna nagar

    ReplyDelete

  7. https://www.newdaypuppies.com/
    https://www.newdaypuppies.com/teacup-yorkie-puppies-for-sale/
    https://www.newdaypuppies.com/tea-cup-yorkie-puppy-for-sale/
    https://www.newdaypuppies.com/yorkshire-terrier-for-sale-near-me/
    https://www.newdaypuppies.com/yorkie-terrier-puppy-for-sale-near-me/

    ReplyDelete
  8. This post is so interactive and informative.keep update more information...
    Java Training in Tambaram
    java course in tambaram

    ReplyDelete
  9. youtube link to mp3 songs
    vimeo youtube to mp3
    youtube video converter to mp3 iphone
    which youtube to mp3 converter is safe
    simple youtube to mp3/mp4 converter firefox
    https://yttomp3.pro/
    websites for converting youtube videos to mp3 free
    youtube to mp3 converter online free fast
    free youtube to mp3 conversor ogg
    youtube to mp3 downloader.

    ReplyDelete
  10. Great experience I got good information from your blog. Ziyyara’s expert phonics tutors, available for personalized one-to-one sessions, create a harmonious learning experience that tunes into your child's unique needs.
    visit phonics home tutor

    ReplyDelete
Previous Post Next Post