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 am feeling great to read this.you gave a nice info for us.please update more.
ReplyDeleteJAVA Training in Chennai
Java training institute in chennai
Selenium Training in Chennai
Hadoop Training in Chennai
Python Training in Chennai
Software testing training in chennai
JAVA Training in Chennai
Java Training in Velachery
Great Article android based projects
DeleteJava 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
The article is so informative. This is more helpful for our
ReplyDeletemagento training course in chennai
magento training institute in chennai
magento 2 training in chennai
magento development training
magento 2 course
magento developer training
Thanks for sharing.
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.
ReplyDeleteMagento Development Training Course in Chennai Zuan Education
Selenium Training Course in Chennai Zuan Education
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.
ReplyDeleteDigital Marketing Course In Kolkata
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.
ReplyDeleteMagento Development Training Course in Chennai Zuan Education
Selenium Training Course in Chennai Zuan Education
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
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 Training Institute in Bangalore
ReplyDeleteExcellent Blog! I would like to thank for the efforts you have made in writing this post.
Best Data Science Courses in Bangalore
I have recently started a blog, the info you provide on this site has helped me greatly. Thanks for all of your time & work.
ReplyDeleteData Science Course in Hyderabad | Data Science Training in Hyderabad
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
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.
ReplyDeleteonline 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 |
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/
Thanks for the Information.Interesting stuff to read.Great Article.
ReplyDeleteI enjoyed reading your post, very nice share.
Data Science Course Training in Hyderabad
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
ReplyDeleteWonderful 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.
ReplyDelete360DigiTMG Tableau Course
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.
ReplyDelete360DigiTMG Business Analytics Course
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
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
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.
ReplyDeleteData 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
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
ReplyDeleteThis Blog is very useful and informative.
ReplyDeletecertification of data science
Nice and very informative blog, glad to learn something through you.
ReplyDeletedata science course in delhi
Fantastic article with valuable information found very useful looking forward for next blog thank you.
ReplyDeleteData Science Course in Bangalore
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
I have to search sites with relevant information ,This is a
ReplyDeletewonderful blog,These type of blog keeps the users interest in
the website, i am impressed. thank you.
Data Science Course in Bangalore
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
You have done a amazing job with you website. ExcelR Data Analyst Course
ReplyDelete
ReplyDeleteI 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
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.
ReplyDeletedata scientists training
Thanks for posting the best information and the blog is very informative.Data science course in Faridabad
ReplyDeleteI 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!
ReplyDeletedata analytics course in bangalore
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.
ReplyDeleteartificial intelligence course in pune
Nice Blog. Thanks for Sharing this useful information...
ReplyDeleteData science training in chennai
Data science course in chennai
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
Fantastic blog with excellent information and valuable content just added your blog to my bookmarking sites thank for sharing.
ReplyDeleteData Science Course in Chennai
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.
ReplyDeleteData Science Training in Chennai
We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work
ReplyDeleteData Science Training in Hyderabad
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!
ReplyDeleteData Science Training in Bangalore
Informative blog
ReplyDeletedata scientist course 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.
ReplyDeletedata science training 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
Informative blog
ReplyDeleteData Science Course in India
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.
ReplyDeleteData Science Training in Chennai
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.
ReplyDelete1) 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.
Excellent site with great content and very informative. I would like to thank you for the efforts you have made in writing.
ReplyDeleteData Science Training in Bangalore
ReplyDeleteVery awesome!!! When I searched for this I found this website at the top of all blogs in search engines.
business analytics course
Fantastic Site with useful and unique content looking forward to the next update thank you.
ReplyDeleteData Science Training in Hyderabad
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 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.
ReplyDeletedata scientist course in bangalore