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.

83 Comments

  1. 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
  2. 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

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

    ReplyDelete
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. This post is very simple to read and appreciate without leaving any details out. Great work!
    data scientist course noida

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

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

    ReplyDelete
  15. 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
  16. 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
  17. 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
  18. 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
  19. Fantastic Site with useful and unique content looking forward to the next update thank you.
    Data Science Training in Hyderabad

    ReplyDelete
  20. 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
  21. 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
  22. 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
  23. This was definitely one of my favorite blogs. Every post published did impress me.
    data scientist course in hyderabad

    ReplyDelete
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. This post is very simple to read and appreciate without leaving any details out. Great work!
    digital marketing courses in hyderabad with placement

    ReplyDelete
  31. I bookmarked your website because this site contains valuable information. I am very satisfied with the quality and the presentation of the articles. Thank you so much for saving great things. I am very grateful for this site.

    Data Science Training in Bangalore

    ReplyDelete
  32. Always so interesting to visit your site.What a great info, thank you for sharing. this will help me so much in my learning
    data science certification

    ReplyDelete
  33. Wonderful blog post. This is absolute magic from you! I have never seen a more wonderful post than this one. You've really made my day today with this. I hope you keep this up!
    data scientist training and placement in hyderabad

    ReplyDelete
  34. Nice to be seeing your site once again, it's been weeks for me. This article which ive been waited for so long. I need this guide to complete my mission inside the school, and it's same issue together along with your essay. Thanks, pleasant share.
    Data Science training in Bangalore

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

    ReplyDelete
  36. This was not just great in fact this was really perfect your talent in writing was great.
    business analytics course

    ReplyDelete
  37. 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
  38. Wow that was odd. I just wrote an really long comment but after I
    clicked submit my comment didn't show up. Grrrr... well I'm
    not writing all that over again. Regardless, just wanted to say excellent blog!

    Feel free to visit my web blog :: 안마

    ReplyDelete
  39. I was looking for information on the internet, and I found your blog. I'm impressed with how informative it is!
    AWS Training in Hyderabad
    AWS Course in Hyderabad

    ReplyDelete
  40. I am searching for and I love to post a remark that "The substance of your post is wonderful" Great work!
    data science training in malaysia

    ReplyDelete
  41. Get trained on data science course in hyderabad by real-time industry experts and excel your career with Data Science training by Technology for all. #1 online data science Training in hyderabad

    ReplyDelete
  42. Data science course with placements in hyderabad
    we provide classroom training on IBM certified Data Science at hyderabad for the individuals who believe hand-held training. We teach as per the Indian Standard Time (IST) with In-depth practical Knowledge on each topic in classroom training, 80 – 90 Hrs of Real-time practical training classes. There are different slots available on weekends or weekdays according to your choices.

    ReplyDelete
  43. Thanks for posting the best information and the blog is very good.data science course in Lucknow

    ReplyDelete
  44. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Analytics Course

    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 enedevors.
    iot course in bangalore

    ReplyDelete
  46. Cool you write, the information is very good and interesting, I'll give you a link to my site.
    data science courses in chennai

    ReplyDelete
  47. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing. data scientist course in lucknow

    ReplyDelete
  48. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.
    cloud computing in hyderabad

    ReplyDelete
  49. Very good article and informative content. Keep updating more blogs with us.
    Data Science Training Institute in Hyderabad with Placements

    ReplyDelete
  50. Excellent post, very informative. I wonder why
    other specialists of this sector don’t realize this.
    You must continue your writing. I am just confident, you possess a fantastic readers’ base already!
    고스톱

    ReplyDelete
  51. I really like what you guys tend to be up too.
    This type of clever work and coverage! Keep up the superb works
    guys I’ve added you guys to blogroll
    토토사이트

    ReplyDelete
  52. I recently found a lot of useful information on your website, especially this blog page. Among the lots of comments on your articles. Thanks for sharing.
    data analytics training in hyderabad

    ReplyDelete
  53. Truly, this article is really one of the very best in the history of articles. I am a antique ’Article’ collector and I sometimes read some new articles if I find them interesting. And I found this one pretty fascinating and it should go into my collection. Very good work!
    cloud courses in hyderabad

    ReplyDelete
  54. I at last discovered extraordinary post here.I will get back here. I just added your blog to my bookmark locales. thanks.Quality presents is the vital on welcome the guests to visit the page, that is the thing that this website page is giving.business analytics course in gurgaon

    ReplyDelete
  55. I think this is a standout amongst the most critical data for me. What"s more, i"m happy perusing your article. Be that as it may, ought to comment on some broad things artificial intelligence training in delhi

    ReplyDelete
  56. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work thank you.
    Cloud Computing Training in Bangalore

    ReplyDelete
  57. I was looking for another article by chance and found your article오공슬롯 I am writing on this topic, so I think it will help a lot. I leave my blog address below. Please visit once.

    ReplyDelete
  58. Informative and knowledgeable content, big thumbs up for your article. Keep sharing more stuff like this. Thank you.
    Best Data Science Training in Hyderabad

    ReplyDelete
  59. 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
  60. 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
    business analytics course in faridabad

    ReplyDelete
  61. This is an excellent post I seen thanks to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post.Data Science Course in Vadodara

    ReplyDelete
  62. Extremely useful information which you have shared here about mobile app development in usa. This is a great way to enhance knowledge for us, and also beneficial for us. Thank you for sharing an article like this.

    ReplyDelete
  63. When I read an article on this topic, 메가슬롯 the first thought was profound and difficult, and I wondered if others could understand.. My site has a discussion board for articles and photos similar to this topic. Could you please visit me when you have time to discuss this topic?

    ReplyDelete
  64. I read your excellent blog post. It's a great job. I enjoyed reading your post for the first time, thank you.
    Data Science Institutes in Bangalore

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

    Data Scientist Training in Bangalore

    ReplyDelete
  66. Awesome message. I coincidentally found your blog and needed to say that I truly appreciated perusing your articles. At any rate, I will buy into your feed and trust you post again soon. https://writeablog.net/ravettldlu/mallika-sherawat-is-considered-a-youth-icon-inside-the-indian-motion-picture

    ReplyDelete
  67. This is moreover a by and large brilliant post which I genuinely thoroughly enjoyed scrutinizing. It isn't every day that I have the probability to see something like this.. http://augustycbv917.yousher.com/think-you-re-cut-out-for-doing-email-marketing-examples-b2b-take-this-quiz

    ReplyDelete
  68. We've noticed that this article is well-informed, in my opinion. The essay is beneficial to us, and your writing is exceptional. We appreciate you sharing this content.hire coldfusion developers

    ReplyDelete
  69. Appreciate you for offering helpful details and an informative content.

    ReplyDelete
  70. The practical advice shared in this blog post for building a career in data science is invaluable.data scientist course in hyderabad

    ReplyDelete
Previous Post Next Post