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.

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

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

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

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

    ReplyDelete
  13. 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
  14. 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
  15. This post is very simple to read and appreciate without leaving any details out. Great work!
    digital marketing courses in hyderabad with placement

    ReplyDelete
  16. 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
  17. This was not just great in fact this was really perfect your talent in writing was great.
    business analytics course

    ReplyDelete
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. Very good article and informative content. Keep updating more blogs with us.
    Data Science Training Institute in Hyderabad with Placements

    ReplyDelete
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. 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
  35. 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
  36. 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
  37. 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
  38. Appreciate you for offering helpful details and an informative content.

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

    ReplyDelete
  40. Play around with the remainder of the new year. You're doing a generally excellent work. Edgerunner David jacket

    ReplyDelete
Previous Post Next Post