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

80 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. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete
  3. Nice article. I liked very much. All the informations given by you are really helpful for my research. keep on posting your views.
    ccna course in Chennai
    ccna Training in Chennai
    ccna Training institute in Chennai
    ccna institute in Chennai
    Best CCNA Training Institute in Chennai

    ReplyDelete
  4. Data Science course in chennai

    I am glad that I have visited this blog. Really helpful, eagerly waiting for more updates.

    ReplyDelete
  5. 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
  6. Truly overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. Much obliged for sharing.business analytics training

    ReplyDelete
  7. 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
  8. Great blog.thanks for sharing such a useful information
    Big Data Hadoop Training

    ReplyDelete
  9. Fantastic article I ought to say and thanks to the info. Instruction is absolutely a sticky topic. But remains one of the top issues of the time. I love your article and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  10. Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.
    Data Analytics Course in Bangalore

    ReplyDelete
  11. Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .
    Data Analytics training in Bangalore

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

    ReplyDelete
  13. This post is so interactive and informative.keep update more information...
    graphic design courses in Porur
    graphic design courses in Chennai

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

    ReplyDelete
  15. https://www.chihuahuapuppiesforsale1.com/
    https://www.chihuahuapuppiesforsale1.com/chihuahua-puppies-for-sale-near-me/
    https://www.chihuahuapuppiesforsale1.com/chihuahua-for-sale/
    https://www.chihuahuapuppiesforsale1.com/chihuahua-puppies-near-me/
    https://www.chihuahuapuppiesforsale1.com/teacup-chihuahuas-for-sale/

    ReplyDelete

  16. 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
  17. Are Yorkies good for first time dog owners?
    yorkshire terrier for sale
    The Yorkshire terrier is a great starter dog for those who want a little lap dog. This breed is affectionate towards its owner and may even act protective around strangers. teacup yorkie puppies for sale The Yorkie has a moderate energy level and only needs basic exercise.

    Are Yorkie Poo good for first time owners?
    tea cup yorkie puppy for sale
    It is the ideal pet for people who don't want to deal with pet fur at home or in their cars. This is the ideal pet for first time dog owners, since it trains easily and needs only moderate grooming maintenance. Yorkie poos are good with kids. It will play with them, is energetic and affectionate.

    Are Yorkie hypoallergenic? Yes
    yorkshire terrier for sale near me
    How much does a Yorkie Poo puppy cost?
    Yorkie Poo puppies range in price from $1,000 to $3,500, depending on the puppy's coloring and the breeder.

    Are Yorkie Poos good dogs?
    yorkie puppies for sale
    The Yorkipoo is a gentle and loving dog who can do well with children. He's not recommended for homes with very young children, since he can be easily injured when improperly handled. A Yorkipoo can make an excellent companion for an older, more considerate child.

    ReplyDelete
  18. Is a Yorkie a good family dog?
    Yorkies, like many other Toy breeds, make good pets for people; they're especially good for senior citizens, people with medical issues, and those who may worry about the size and strength of a larger dog. ... They're loving, devoted, and very affectionate: This makes them great personal companions and good family pets.

    Do Yorkshire terriers bark a lot?
    yorkies for sale
    Yorkshire Terriers are little dogs with huge personalities. teacup yorkies for sale With those huge personalities come a fierce territorial bark. Any time your phone rings, someone speaks or knocks on your door, or your doorbell chimes, your Yorkshire Terrier will likely bark. Outside noises aren't even required for barking for some Yorkies. yorkie puppies for sale

    How much do Yorkshire terriers cost?
    yorkie for sale near me
    Typical Yorkie prices range from $1,500 to $3,000, but the cost can fall far below or above that range. Prices will vary based on the puppy's lineage, appearance, health, and the breeder. It's also possible to adopt an older Yorkie for significantly less through a rescue shelter. yorkie for sale

    ReplyDelete
  19. Is a Yorkie a good family dog?
    yorkie puppies for sale
    Yorkies, like many other Toy breeds, make good pets for people; they're especially good for senior citizens, people with medical issues, and those who may worry about the size and strength of a larger dog. ... They're loving, devoted, and very affectionate: This makes them great personal companions and good family pets.

    Do Yorkshire terriers bark a lot?
    teacup yorkie for sale
    Yorkshire Terriers are little dogs with huge personalities. yorkies for sale With those huge personalities come a fierce territorial bark. Any time your phone rings, someone speaks or knocks on your door, or your doorbell chimes, your Yorkshire Terrier will likely bark. Outside noises aren't even required for barking for some Yorkies. yorkie for sale near me

    How much do Yorkshire terriers cost?
    yorkies puppy for sale
    Typical Yorkie prices range from $1,500 to $3,000, but the cost can fall far below or above that range. Prices will vary based on the puppy's lineage, appearance, health, and the breeder. It's also possible to adopt an older Yorkie for significantly less through a rescue shelter.
    https://www.yorkiespuppiessale.com/

    ReplyDelete
  20. Are Chihuahua puppies hard to train?
    chihuahua puppies for sale
    Chihuahuas are intelligent, strong-minded dogs that like to do their own thing. This can make them stubborn, earning them a reputation for being hard to train. However, reward-based training methods do appeal to a Chihuahua and there's no reason why they can't be trained to be obedient, just as with any dog

    Chihuahuas Have Fun, Playful Personalities
    teacup chihuahuas for sale
    Though chihuahuas are loyal and affectionate with their owners, they are anything but dull and love to play! However, no two chihuahuas are the same, so if you have a friend with a chihuahua, your pet is likely to be quite different and unique

    What food is bad for Chihuahua?
    teacup chihuahua for sale
    Foods Your Chihuahua Shouldn't Eat
    Alcohol. Alcohol (ethanol) is highly toxic to dogs. ...
    Caffeine. Coffee is one of the world's most popular beverages, with roughly 83% of the United States adult population consuming it on a daily basis. ...
    Chocolate. ...
    Some Fruit Seeds, Pits and Cores. ...
    Garlic. ...
    Grapes and Raisins. ...
    Hops. ...
    Onions.
    chihuahua puppy for sale

    The easiest way to adopt a Chihuahua would be through a rescue that specializes in Chihuahuas. A great place to start would be by starting a breed search on https://www.chihuahuapuppiesforsale1.com/chihuahua-puppies-for-sale-near-me/. The search will show you all the available Chihuahuas in your area.
    chihuahua puppies for sale near me

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

    ReplyDelete
  22. Takipçi satın al! Sende aşağıdaki bağlantıları kullanarak en güvenli takipçi satın alma sitesi Takipcidukkani.com ile takipçi satın al. Tıkla hemen sende instagram takipçi satın al:

    1- takipçi satın al

    2- takipçi satın al

    3 - takipçi satın al

    ReplyDelete
  23. APTRON is a renowned institute in Delhi that offers a range of IT and cybersecurity courses, including ethical hacking. The institute has a team of experienced trainers who provide hands-on training and guidance to students.Ethical hacking Institute in Delhi

    ReplyDelete
Previous Post Next Post