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?
Step 2:- Create anonymous block to execute the stored procedure.Execute following blocks and see the output as total salary.
Step 2:- Now write execution block from where program unit with REF CURSOR is tested.
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):
=====End of post=======
Read : Difference between SYS_REFCURSOR and REF CURSOR.
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.
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.
ReplyDeleteData Science Course
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.
ReplyDeleteData Science Training
ReplyDeleteExcellent Blog! I would like to thank for the efforts you have made in writing this post.
Best Data Science Courses in Bangalore
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!
ReplyDeleteBusiness Analytics Training in Hyderabad | Business Analytics Course in Hyderabad
https://digitalweekday.com/
ReplyDeletehttps://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
ReplyDeletehttps://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
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
ReplyDeleteThanks 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 .
ReplyDeleteGreat article with excellent information found resourceful and enjoyed reading it thank you, looking forward for next blog.
ReplyDeletetypeerror nonetype object is not subscriptable
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…
ReplyDeletedata science course in Hyderabad
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.
ReplyDeletedata science training in Hyderabad
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.
ReplyDeleteBusiness Analytics Course in Bangalore
Aivivu vé máy bay giá rẻ
ReplyDeleteve may bay tet 2021 gia re
vé máy bay đi Mỹ giá bao nhiêu
vé máy bay đi Pháp khứ hồi
săn vé máy bay rẻ đi hàn quốc
vé máy bay vietnam airline đi nhật bản
vé máy bay đi Anh bao nhiêu tiền
Big Aquatic best it solution in gorakhpur
ReplyDeleteNino Nurmadi, S.Kom
ReplyDeleteNino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
This Blog is very useful and informative.
ReplyDeletecertification of data science
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.
ReplyDeleteData Analyst Course
This post is very simple to read and appreciate without leaving any details out. Great work!
ReplyDeletedata scientist course noida
Your content is very unique and understandable useful for the readers keep update more article like this.
ReplyDeletedata science training in yelahanka
You totally coordinate our desire and the assortment of our data.
ReplyDeletedata scientist malaysia
Đặt vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ
chuyến bay cứu trợ mỹ về việt nam
khi nào có chuyến bay từ đức về việt nam
vé máy bay nga về việt nam
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.
ReplyDeleteData Science Course in Bangalore
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.
ReplyDeletedata analytics training in bangalore
Informative blog
ReplyDeletedata scientist course in Bangalore
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.
ReplyDeletedata analytics course in bangalore
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.
ReplyDeletebusiness analytics course in bangalore
Fantastic Site with useful and unique content looking forward to the next update thank you.
ReplyDeleteData Science Training in Hyderabad
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.
ReplyDeletedata analytics training in bangalore
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.
ReplyDeletecyber security training in bangalore
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.
ReplyDeletebest data science courses in bangalore
This was definitely one of my favorite blogs. Every post published did impress me.
ReplyDeletedata scientist course in hyderabad
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.
ReplyDeleteArtificial Intelligence course in Chennai
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!
ReplyDeleteData Science Courses in Bangalore
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.
ReplyDeletebusiness analytics course
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.
ReplyDeletedata analytics courses in bangalore
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.
ReplyDeletedigital marketing courses in hyderabad with placement
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.
ReplyDeleteData Science Course in Bangalore
Nice content keep it up
ReplyDeleteThis post is very simple to read and appreciate without leaving any details out. Great work!
ReplyDeletedigital marketing courses in hyderabad with placement
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.
ReplyDeleteData Science Training in Bangalore
Always so interesting to visit your site.What a great info, thank you for sharing. this will help me so much in my learning
ReplyDeletedata science certification
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!
ReplyDeletedata scientist training and placement in hyderabad
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.
ReplyDeleteData Science training in Bangalore
Thanks for posting the best information and the blog is very important.digital marketing institute in hyderabad
ReplyDeleteThis was not just great in fact this was really perfect your talent in writing was great.
ReplyDeletebusiness analytics course
Excellent Blog! I would like to thank you for the efforts you have made in writing this post. Gained lots of knowledge.
ReplyDeleteData Analytics Course
Excellent Blog. Good content and informative. I really liked it. Keep sharing more.
ReplyDeleteMachine Learning Training with Placements in Hyderabad
Artificial Intelligence Course with Placements in Hyderabad
Informative article. Thanks for sharing with us.keep it up.
ReplyDeletedata scientist course in aurangabad
Wow that was odd. I just wrote an really long comment but after I
ReplyDeleteclicked 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 :: 안마
I was looking for information on the internet, and I found your blog. I'm impressed with how informative it is!
ReplyDeleteAWS Training in Hyderabad
AWS Course in Hyderabad
I am searching for and I love to post a remark that "The substance of your post is wonderful" Great work!
ReplyDeletedata science training in malaysia
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
ReplyDeleteData science course with placements in hyderabad
ReplyDeletewe 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.
Thanks for posting the best information and the blog is very good.data science course in Lucknow
ReplyDeleteI feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
ReplyDeleteData Analytics Course
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.
ReplyDeleteiot course in bangalore
Cool you write, the information is very good and interesting, I'll give you a link to my site.
ReplyDeletedata science courses in chennai
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
ReplyDeleteExtremely 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.
ReplyDeletecloud computing in hyderabad
Very good article and informative content. Keep updating more blogs with us.
ReplyDeleteData Science Training Institute in Hyderabad with Placements
Excellent post, very informative. I wonder why
ReplyDeleteother specialists of this sector don’t realize this.
You must continue your writing. I am just confident, you possess a fantastic readers’ base already!
고스톱
I really like what you guys tend to be up too.
ReplyDeleteThis type of clever work and coverage! Keep up the superb works
guys I’ve added you guys to blogroll
토토사이트
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.
ReplyDeletedata analytics training in hyderabad
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!
ReplyDeletecloud courses in hyderabad
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
ReplyDeleteI 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
ReplyDeleteWe are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work thank you.
ReplyDeleteCloud Computing Training in Bangalore
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.
ReplyDeleteInformative and knowledgeable content, big thumbs up for your article. Keep sharing more stuff like this. Thank you.
ReplyDeleteBest Data Science Training in Hyderabad
Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.
ReplyDeleteData Analytics Course in Bangalore
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
ReplyDeletebusiness analytics course in faridabad
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
ReplyDeleteExtremely 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.
ReplyDeleteWhen 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?
ReplyDeleteI read your excellent blog post. It's a great job. I enjoyed reading your post for the first time, thank you.
ReplyDeleteData Science Institutes in Bangalore
What an incredible message this is. Truly one of the best posts I have ever seen in my life. Wow, keep it up.
ReplyDeleteData Scientist Training in Bangalore
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
ReplyDeleteThis 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
ReplyDeleteNice article...Thanks for sharing...
ReplyDeleteData science training in Pune
đặt vé Eva Air
ReplyDeletephí đổi vé máy bay Japan Airlines
giá mua thêm hành lý ký gửi China Airlines
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
ReplyDeleteAppreciate you for offering helpful details and an informative content.
ReplyDeleteThe practical advice shared in this blog post for building a career in data science is invaluable.data scientist course in hyderabad
ReplyDelete