Sep 25, 2015

How to test a oracle stored procedure with ref cursor from SQL*PLUS or SQL Developer

Oracle stored procedure is one kind of PL/SQL program unit. Consider the following cases :
1. stored procedure with scalar type - NUMBER, VARCHAR
2. stored procedure with REF CURSOR or SYS_REFCURSOR. What is difference between REF CURSOR and SYS_REFCURSOR?

Execute/Test stored procedure with primitive type:-

Step 1:- Create a stored procedure with primitive type.Below is sample code for the same ignore this step if you already have some stored procedure created. Execute it, SQL*PLUS/SQL Develope worksheet.
create or replace procedure get_employee_total_saalry 
(
  base_salary IN NUMBER 
, bonus  IN NUMBER 
, total_sal OUT NUMBER 
) as 
begin
  total_sal :=base_salary + .12*base_salary + bonus; 
end get_employee_total_saalry;

Step 2:- Create anonymous block to execute the stored procedure.Execute following blocks and see the output as total salary.
--Stored procedure having primitive type as paraemter 
declare
type rc is ref cursor;
v_base_sal NUMBER(5) := 1000;
v_bonus NUMBER(5)  := 2300;
v_total NUMBER(10) ;
begin
--call stored procedure with two IN and one OUT type parameter
HR.get_employee_total_saalry(v_base_sal,v_bonus,v_total);
    dbms_output.put_line(' Total sal is ' ||v_total);
end;

Execute/Test stored procedure with REF_CURSOR/SYS_REFCURSOR type:-

Step 1:- Create a stored procedure  with REF CURSOR. Below is the sample code for the same, ignore this step if you already have procedure created.
create or replace procedure get_employees_name(
v_deptId_in NUMBER,
v_cur OUT SYS_REFCURSOR ) 
IS
begin
  open v_cur for select FIRST_NAME,LAST_NAME from
        employees where DEPARTMENT_ID = v_deptId_in;
end get_employees_name;

Step 2:- Now write execution block from where program unit with REF CURSOR is tested.
--using refcursor --
declare
-declare ref cursor type 
type rc is ref cursor;
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur rc; -declare ref cursor variable
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,v_cur);
dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
dbms_output.put_line('---------------------------------');
LOOP
    FETCH v_cur INTO v_fname, v_lname;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(v_fname || '     ' || v_lname);
  END LOOP;
  CLOSE v_cur;
end;

Using SYS_REFCURSOR also, we can write program blocks to test same stored procedure. (Remember, SYS_REFCURSOR is just a predefined weak REF CURSOR, so we do not have to declare a cursor type like "type rc is ref cursor". Following is testing program(differences from above program has been highlighted):
declare
--type rc is ref cursor; NOT required 
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur SYS_REFCURSOR; --rc replaced with predefined type SYS_REFCURSOR
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,v_cur);
dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
dbms_output.put_line('---------------------------------');
LOOP
    FETCH v_cur INTO v_fname, v_lname;
    EXIT WHEN v_cur%NOTFOUND;
    
    dbms_output.put_line(v_fname || '      ' || v_lname);
  END LOOP;
  CLOSE v_cur;
end;

Alternate way execute stored procedure with REF_CURSOR/SYS_REFCURSOR type:-

Declare the cursor variable of type refcursor and run stored procedure with exec. In SQL developer/SQL*PLUS it can be executed like this :-
--cursor variable declaration 
variable v_emp_cur refcursor;
--execute procedure 
execute get_employees_name(90,:v_emp_cur);
--display result referenced by ref cursor.
print v_emp_cur;

=====End of post=======

Read : Difference between SYS_REFCURSOR and REF CURSOR.
Location: Hyderabad, Telangana, India

123 comments:

  1. Replies
    1. Great Article android based projects

      Java Training in Chennai

      Project Center in Chennai

      Java Training in Chennai

      projects for cse

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. Great efforts put to publish these kinds of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create great relations. You’re doing an excellent job. Keep it up.

    Magento Development Training Course in Chennai Zuan Education

    Selenium Training Course in Chennai Zuan Education

    ReplyDelete
  3. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Digital Marketing Course In Kolkata

    ReplyDelete
  4. Great efforts put to publish these kinds of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create great relations. You’re doing an excellent job. Keep it up.

    Magento Development Training Course in Chennai Zuan Education

    Selenium Training Course in Chennai Zuan Education

    ReplyDelete
  5. I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.

    Data Science Course

    ReplyDelete
  6. It's really nice and meanful. it's really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information.

    Data Science Training

    ReplyDelete
  7. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Training Institute in Bangalore

    ReplyDelete

  8. Excellent Blog! I would like to thank for the efforts you have made in writing this post.
    Best Data Science Courses in Bangalore

    ReplyDelete
  9. I have recently started a blog, the info you provide on this site has helped me greatly. Thanks for all of your time & work.
    Data Science Course in Hyderabad | Data Science Training in Hyderabad

    ReplyDelete
  10. You are in point of fact a just right webmaster. The website loading speed is amazing. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterpiece. you have done a fantastic activity on this subject!
    Business Analytics Training in Hyderabad | Business Analytics Course in Hyderabad

    ReplyDelete
  11. NICE BLOG!!! Your blog is very informative for us. I would really like to come back again right here for likewise good articles or blog posts.

    online store developer in USA |Web Developer in USA | E-Store Developer in USA | Website Designer in California |Website Developer in California |Portal Developer in California |

    ReplyDelete
  12. Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.
    Data Science Course Training in Hyderabad

    ReplyDelete
  13. Found your post interesting to read. I cant wait to see your post soon. Good Luck for the upcoming update. This article is really very interesting and effective, data science course

    ReplyDelete
  14. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    360DigiTMG Tableau Course

    ReplyDelete
  15. Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.

    360DigiTMG Business Analytics Course

    ReplyDelete
  16. Very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data sciecne course in hyderabad

    ReplyDelete
  17. Thanks for sharing this great information on Oracle SOA. Actually I was looking for the same information on internet for SOA Interview Questions and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more about SOA by attending Oracle SOA Training training .

    ReplyDelete
  18. Great article with excellent information found resourceful and enjoyed reading it thank you, looking forward for next blog.
    typeerror nonetype object is not subscriptable

    ReplyDelete
  19. I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon…
    data science course in Hyderabad

    ReplyDelete
  20. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site.
    data science training in Hyderabad

    ReplyDelete
  21. It fully emerged to crown Singapore's southern shores and has undoubtedly put it on the world residential monument map. Still, I scored more points than I have in one season for GS. I think it would be hard to find someone with the same consistency that I have had over the years, so I'm happy.

    Business Analytics Course in Bangalore

    ReplyDelete
  22. I really enjoy reading all of your blogs. I just wanted to let you know that you have people like me who appreciate your work. Definitely a great article. Congratulations! The information you have provided is very helpful.

    Data Analytics Course in Bangalore

    ReplyDelete
  23. Thank you for this wonderful post. This is really amazing. I am looking after this type post. Finally, I am find it here. data science course in Hyderabad

    ReplyDelete
  24. Nice and very informative blog, glad to learn something through you.
    data science course in delhi

    ReplyDelete
  25. Fantastic article with valuable information found very useful looking forward for next blog thank you.
    Data Science Course in Bangalore

    ReplyDelete
  26. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  27. This post is very simple to read and appreciate without leaving any details out. Great work!
    data scientist course noida

    ReplyDelete
  28. Your content is very unique and understandable useful for the readers keep update more article like this.
    data science training in yelahanka

    ReplyDelete
  29. I have to search sites with relevant information ,This is a
    wonderful blog,These type of blog keeps the users interest in
    the website, i am impressed. thank you.
    Data Science Course in Bangalore

    ReplyDelete
  30. You totally coordinate our desire and the assortment of our data.
    data scientist malaysia

    ReplyDelete
  31. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  32. You have done a amazing job with you website. ExcelR Data Analyst Course

    ReplyDelete

  33. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.
    business analytics course

    ReplyDelete
  34. Through this post, I realize that your great information in playing with all the pieces was useful. I inform that this is the primary spot where I discover issues I've been looking for. You have a cunning yet alluring method of composing.
    data scientists training

    ReplyDelete
  35. Thanks for posting the best information and the blog is very informative.Data science course in Faridabad

    ReplyDelete
  36. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  37. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    artificial intelligence course in pune

    ReplyDelete
  38. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  39. Fantastic blog with excellent information and valuable content just added your blog to my bookmarking sites thank for sharing.
    Data Science Course in Chennai

    ReplyDelete
  40. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  41. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work

    Data Science Training in Hyderabad

    ReplyDelete
  42. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Training in Bangalore

    ReplyDelete
  43. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    data science training in bangalore

    ReplyDelete
  44. I want to leave a little comment to support and wish you the best of luck.we wish the best best of luck in all your blogging endeavors.
    data analytics course in bangalore

    ReplyDelete
  45. I Want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging endeavors.
    business analytics course in bangalore

    ReplyDelete
  46. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  47. I must appreciate you for providing such valuable content for us. To make our dreams a reality, everyone has to put up a hard struggle in their life. Here I am making my small effort to arrange up the list of highest paying Jobs in India. We provide one of the best courses for you.
    1) Data Science course in Delhi
    2) Artificial Intelligence Course in Delhi
    3) Machine Learning course in Delhi
    4) Business Analytics courses in Delhi
    5) Digital marketing course in Delhi
    6) Full Stack Software Developer course in Delhi.

    ReplyDelete
  48. Excellent site with great content and very informative. I would like to thank you for the efforts you have made in writing.
    Data Science Training in Bangalore

    ReplyDelete

  49. Very awesome!!! When I searched for this I found this website at the top of all blogs in search engines.

    business analytics course

    ReplyDelete
  50. Fantastic Site with useful and unique content looking forward to the next update thank you.
    Data Science Training in Hyderabad

    ReplyDelete
  51. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data analytics courses in bangalore

    ReplyDelete
  52. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    data scientist course in bangalore

    ReplyDelete
  53. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Training in Bangalore

    ReplyDelete
  54. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    cyber security training in bangalore

    ReplyDelete
  55. I Want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging endeavors.
    data science certification in banagalore

    ReplyDelete
  56. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  57. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science in bangalore

    ReplyDelete
  58. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    cyber security training in bangalore

    ReplyDelete
  59. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  60. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    data scientist course in bangalore

    ReplyDelete
  61. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science course fees in bangalore

    ReplyDelete
  62. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  63. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  64. Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.

    data analytics courses in bangalore with placement

    ReplyDelete
  65. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    best data science courses in bangalore

    ReplyDelete
  66. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging endeavours.
    data science certification in bangalore

    ReplyDelete

  67. Really impressed! Everything is very open and very clear clarification of issues. It contains true facts. Your website is very valuable. Thanks for sharing.

    business analytics course

    ReplyDelete
  68. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging endeavours.
    data science course in bangalore with placement

    ReplyDelete
  69. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  70. Thanks for posting the best information and the blog is very helpful.data science institutes in hyderabad

    ReplyDelete
  71. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  72. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    cyber security training in bangalore

    ReplyDelete
  73. I see the greatest content on your blog and I extremely love reading them.

    best data science institute in hyderabad

    ReplyDelete
  74. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data analytics courses in bangalore

    ReplyDelete
  75. This was definitely one of my favorite blogs. Every post published did impress me.
    data scientist course in hyderabad

    ReplyDelete
  76. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.


    best data science institute in hyderabad

    ReplyDelete
  77. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  78. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  79. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    best data science courses in bangalore

    ReplyDelete
  80. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Science Course in Chennai

    ReplyDelete
  81. If you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries

    digital marketing courses in hyderabad with placement

    ReplyDelete
  82. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data analytics courses in bangalore

    ReplyDelete
  83. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  84. All of these posts were incredible perfect. It would be great if you’ll post more updates and your website is really cool and this is a great inspiring article.
    Artificial Intelligence course in Chennai

    ReplyDelete
  85. Awesome article. I enjoyed reading your articles. this can be really a good scan for me. wanting forward to reading new articles. maintain the nice work!
    Data Science Courses in Bangalore

    ReplyDelete
  86. Excellent Blog! I would like to thank you for the efforts you have made in writing this post. Gained lots of knowledge.
    Data Analytics Course

    ReplyDelete
  87. Thanks for posting the best information and the blog is very helpful.artificial intelligence course in hyderabad

    ReplyDelete
  88. Awesome article. I enjoyed reading your articles. this can be really a good scan for me. wanting forward to reading new articles. maintain the nice work!
    Data Science Courses in Bangalore

    ReplyDelete
  89. I am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
    Business Analytics Course in Bangalore

    ReplyDelete
  90. This is an excellent post . thanks for sharing it. It is really what I wanted to see. I hope in the future you will continue to share such an excellent post.
    business analytics course

    ReplyDelete
  91. Download Cricket Mazza 11 for Android Live Line & Fastest IPL Score. Get Live Cricket Scores, Scorecard, Commentary, Match Info and Schedules of All International & Domestic Matches, Series wise Stats, Records, Analysis and Facts, Trending News and Tweets, Recent ICC Player and Team Rankings. Also Download best cricket game apps for ios, including Beach Cricket, Cricket T20 Fever 3D, Download Real Cricket mazza 11 and other top answers suggested and ranked.

    ReplyDelete
  92. What an incredible message this is. Truly one of the best posts I have ever seen in my life. Wow, keep it up.
    AI Courses in Bangalore

    ReplyDelete
  93. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    data science course

    ReplyDelete
  94. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.
    business analytics course

    ReplyDelete
  95. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  96. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data analytics courses in bangalore

    ReplyDelete
  97. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    data scientist course in bangalore

    ReplyDelete
  98. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  99. Thanks for posting the best information and the blog is very important.digital marketing institute in hyderabad

    ReplyDelete
  100. It's really nice and meaningful. it's a really cool blog.you have really helped lots of people who visit blogs and provide them useful information
    business analytics course

    ReplyDelete
  101. I think SQL and REST both are actually very useful tools for finding out solutions of some very useful and complex database operations.Although both can’t be used interchangeably.

    SQL Server Load Rest Api

    ReplyDelete
  102. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  103. I am sure that this is going to help a lot of individuals. Keep up the good work. It is highly convincing and I enjoyed going through the entire blog.
    digital marketing courses in hyderabad with placement

    ReplyDelete
  104. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    data science course in chennai

    ReplyDelete
  105. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  106. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  107. This post is very simple to read and appreciate without leaving any details out. Great work!
    digital marketing courses in hyderabad with placement

    ReplyDelete
  108. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Training in Bangalore

    ReplyDelete