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

64 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