Oracle interview question Procedure Vs Function | ORACLE PROCEDURE VS FUNCTIONS

[@sivaveeramallu3645]

All the videos from Siva Academy are super.

[@SivaAcademy]

Welcome bro 🙏

[@gowthamraj1750]

@@SivaAcademy can you please explain difference between execution plan and explain plan

[@SivaAcademy]

Sure, please stay tuned

[@sagarjadhav299]

Very Good and Helpful Information Also Your teaching style is very good Sir

[@SivaAcademy]

Thank you!!! :-)

[@martinsuresh5048]

very helpful information hatsoff sir

[@vivektarab16]

Start 0:34 - Difference between Procedure and Function - Procedure vs Function - A function should always return a value and to return information from a function will be using the return Keyword 0:51 - 1:08 - But in Procedure - we can have any no of return value So that is the reason function can return only 1 value, so may be we can return like an array or any other collection but irrespective of that we can only 1:24 - Functions can be called from Select Statement - The Functions can be called from a Select Statement like the way we used to call the inbuilt functions, we can write our user defined functions and we can call from the Select statement, However Procedures cannot be called from the Select Statement. Mainly functions are used to implement the computational purposes, where as the procedures will be using to implement the logical data flow. 1:47 - Autonomous Transaction - As I mentioned earlier the functions can be called from a select statement but there is a restriction in Oracle like if the function has any DML statement then we cannot call this function in the select statement but there is an exception to this rule - If it is in the autonomous transaction function then it can have a DML statements also. 2:59 - Return Keyword to Return the Value 3:51 - Now we will See Few Examples - Return in Procedure 4:33 - Return in Function 5:20 - How will you identify all the Function which doesnt have a Return Keyword 5:54 - Enhancement in one more Procedure and Function in 12.1 C

[@kunnudev7250]

Great job please continue awesome videos sir

[@SivaAcademy]

Welcome 🙏💐

[@renevillalta27]

Great Videos thanks, just pointing something, functions can be called in SQL statement so far as the function doesn't contain any formal parameter mode OUT or IN OUT

[@SivaAcademy]

Great, Thanks

[@sonukumar-nx8tt]

Very helpful..thanks for amazing video

[@SivaAcademy]

Welcome, thank you

[@akware977]

Thank you so much for the video and sharing knowledge.....

[@SivaAcademy]

Welcome :-) Akshay

[@abhisheknegi8053]

All your videos are very nice easy to understand and having sense.

[@SivaAcademy]

Welcome bro 🙏

[@prathyush1773]

Great Siva. Thank you for your videos. Very clear explanation.

[@SivaAcademy]

You are welcome

[@swamivivekananda-cyclonicm8781]

So unique and insightful

[@SivaAcademy]

Thank you

[@rainbowclubkerala2685]

It's wonderful.. Nicely explaining everything... Thankuu

[@SivaAcademy]

Welcome 😊

[@premgona5587]

RETURN keyword in procedure will take the call to the calling environment and exit from the program In function, RETURN will return the value and exit from the program.... I'm not commenting as you are wrong.. I'm just extending the statement

[@Snakism12]

ok... now u can go to hell

[@sciencemaster4815]

I'm finding your videos very helpful. Thanks.

[@SivaAcademy]

My pleasure, welcome

[@sankareeswaran]

Nice explanation. I have 12plus years experience. But they asked this question.

[@SivaAcademy]

yeah, this happens sometimes :-)

[@vijaydubey1734]

Very helpful Siva,🙏 many thanks

[@SivaAcademy]

Welcome Vijay 💐💐

[@venkatapathiuppalapati9673]

Nice videos air

[@SivaAcademy]

Thank you

[@rajt5218]

Helpful sir

[@SivaAcademy]

Thank you

[@mayurhajare1596]

I have gone this question many time in first technical round

[@bharathipandian45]

Siva, a doubt. If fiction without keyword can be compiled. But we can't use it in both select and inside any other objects like proc function or in any java code?

[@snehadubey2442]

Well explained

[@purushothamanthillaw.sa.pi8483]

very clear post , thanks siva.

[@SivaAcademy]

Welcome 🙂 🙏

[@sgnaneswari2902]

Super explaination

[@SivaAcademy]

Thank you

[@poonamdhamane7394]

Please make video on role for Oracle

[@akashsoni6419]

Hello shiva, your vedios are really awesome, Could you please make a vedio on deterministic Function and pipelines functions with real time examples. Thanks in advance.

[@SivaAcademy]

Sure Akash, Please stay tuned.

[@sreecreativy]

Sir returning keyword in the procedure is not working for me ,,I am using 19 version of Oracle plsql

[@shaphikmulla4149]

Thank you sir ...you are awesome

[@SivaAcademy]

It's my pleasure,Thank you so much :-)

[@brenthawkins5097]

Fun fact. The creator is actually from Decatur, Georgia. He has to use this accent to get views.........just kidding. Great vid. Thanks alot.

[@SivaAcademy]

😀😀 thanks for your comments

[@naseemashaik6457]

Hi Shiva I received this question from my interviewer Q: We can perform all actions inside the procedures then what is the need of functions?

[@SivaAcademy]

Procedure cannot be called in select Statement. However function can be used in select Statement.

[@pradeepverma8277]

nice video

[@SivaAcademy]

Thanks

[@vasubabugunda7947]

Super explanation sir

[@SivaAcademy]

Thank you

[@gowthamraj1750]

Can you please explain difference between execution plan and explain plan

[@sheikabdullah1494]

Sir ...why we go for functions instead of a procedure ...can u pls explain this !?

[@kaththikarthi878]

Sir, why we are using function. Because all operation should be done with procedure. But why using function??????? Pls tell me bro

[@premgona5587]

Please note from 11g onwards, we can use mixed notations when we execute the function. Mixed notation includes, positional notation and named notation. For example if you have a function called FF1 with an IN parameter then at the time of execution we can do as below. SELECT FF1(IP_1 =>5) FROM DUAL; SELECT FF1(5) FROM DUAL;

[@harishas1224]

Hello I’m looking for the starting videos bcz now I’m learning to start from beginning so plz let me know which one is the starting video in all the video like series numbers.

[@SivaAcademy]

Eevery video covers a specific point with respect to interview, you can watch anything in any order, however if you follow the Playlist order, it would be easy for understanding

[@ngundre]

Here you have mentioned functions having dml statement can be called from sql if it's autonomous transaction. Can you explain what is autonomous transaction functions. As per my information, are you referring pragma autonomous declaration in function. Please explain this point how we can call function having dml in sql

[@SivaAcademy]

Please look into this video kzbin.info/www/bejne/r6q1fmCmZ92ZkMU

[@lekhyadivya2626]

Please also make a video on pipelined and parallel functions...

[@SivaAcademy]

Sure please stay tuned 👍

[@shyamdhm]

Primary key does not have null and duplicates but foreign key does have why?

[@vivektarab1959]

3:47 - Start 6:00

[@muralisethi]

Dear Siva, thanks for posting such valuable concepts. Can we have out parameters in function?

[@SivaAcademy]

Yes you can, please look into this video kzbin.info/www/bejne/hqqtZo2tm8dqjbs

[@manishaar3154]

Can we fetch the last 3 records inserted without having an identity column nor date column ?

[@Crazy-Trainings]

Yes u can select top(select count(*) from tabl) from tab1 minus select top(select count(*) from tab1)-3 from tab1;

[@ankursrivastava5148]

Can you please explain 4 th point with example in case of function?

[@karthikeyanviper]

Pls post video related procedure and function in with clause

[@SivaAcademy]

Its already available, please look into this video kzbin.info/www/bejne/qHvYg4qsbNBgsNE

[@praveenamarghade1383]

Thank you for this nice video. I have one query, Can funtion contains DDL statement?

[@SivaAcademy]

Welcome, yes, you can have, however there are few restrictions, for more details please look into this video kzbin.info/www/bejne/r6q1fmCmZ92ZkMU kzbin.info/www/bejne/eXiviHevqdSIm6c

[@VijayKumar-eq2te]

Which book is better for reading for sql plsql.

[@mlipina5324]

Very nice explanations ,Please add some extra volume.

[@SivaAcademy]

@m Lipina, Thanks for your comments, In the recent videos volume has been raised. I will take care in upcoming videos. Regards,Siva.

[@digv0008]

Hi Siva, Where can I get the scripts used in all these videos? Thank you.

[@biswanath4554]

Autonomous transaction function, can you show us some examples

[@thiru601mbt6]

Can we use function inside stored procedure? pls share video on this bro

[@SivaAcademy]

Yes, you can use function within procedure

[@raghavendranramachari488]

Thank you for the video..very useful...But the audio is very low....

[@SivaAcademy]

Welcome, I hope recent videos are fine

[@Ganeshay-09]

With function get_country_name1 (p_country_id varchar2) return varchar2 as l_country_name hr.countries.country_name%type; Begin select country_name into l_country_name from hr.countries where country_id = p_country_id; return l_country_name; End get_country_name1; N1 as ( select E.FIRST_NAME, E.LAST_NAME, E.SALARY, L.country_id from hr.employees E, hr.departments D, hr.locations L where E.DEPARTMENT_ID = 90 and E.DEPARTMENT_ID = D.DEPARTMENT_ID and D.LOCATION_ID = L.LOCATION_ID ) select N1.*, get_country_name1(N1.country_id) country_name from N1; --------------- like this we can call the function using with clause in function

[@divyabaireddy6271]

Please explain about returning multiple values from a function

[@SivaAcademy]

Already posted, please look into this video kzbin.info/www/bejne/hqqtZo2tm8dqjbs

[@nagaswamymp5388]

return refcursor

[@shintojoy317]

1. You can use multiple OUT parameters (Scalar / Complex) 2. You can RETURN REF CURSOR 3. You can RETURN Complex datatypes (Collections, Records, Object Types)

[@manoranjanray8181]

Siva, please create a detailed video on debugging

[@SivaAcademy]

Sure please stay tuned 👍

[@MaheshkumarRevalmath]

Hi, Could you pl share a video on the similarities or difference between Loops and Cursors in PL/SQL?

[@SivaAcademy]

Sure please stay tuned

[@SACHINRATHOD-cs7zs]

How can we add amount from last rows and give total output in current row?

[@SivaAcademy]

Please post sample data and expected output

[@reinecj672]

Can we call procedure inside a function and calling function inside a procedure?

[@SivaAcademy]

Yes you can call both way, however there are few limitations, will explain with example soon, please stay tuned

[@humayunhimu6045]

Can u please tell what is level and connect by

[@SivaAcademy]

Sure, please stay tuned

[@Prasannaprasad6]

thank u sivagaru

[@SivaAcademy]

Welcome 💐

[@bblingamoorthy]

Thanks Siva, could you help me to clarify the following question that was asked during one of my interview discussions ? When a given task can be completed just by using a Procedure, what is use of doing the same task using Package ?

[@SivaAcademy]

Will post a video on advantages of writing with in packages soon, please stay tuned

[@bblingamoorthy]

@@SivaAcademy Thank you very much for your response Siva. Please add the link once posted

[@SivaAcademy]

Please look into this video kzbin.info/www/bejne/g5vIaaN7qbuYa6s

[@bblingamoorthy]

@@SivaAcademy Thank you so much for your effort and timely response. Let me watch.

[@SivaAcademy]

Welcome 👍💐

[@omkardl678]

Could you please explain the high level water mark topic bro

[@SivaAcademy]

Sure bro, please stay tuned

[@shaikrasheed2861]

Is plsql pass by value,pass by reference ?? For example java is allowing pass by value??

[@SivaAcademy]

@Shaik, Ya, in PLSQL too, we can have pass by value & reference, By default all the parameters passed to PLSQL code are pass by value, if you specify "NOCOPY" keyword, it becomes pass by reference, but there is little more to understand about NOCOPY. I will cover in detail about NOCOPY and its effects in some other video. Thanks, Siva

[@shaikrasheed2861]

Thank you sir

[@gsraj511]

Thanks Siva..

[@SivaAcademy]

Welcome George

[@hemantakumar3652]

How to optimize a procedure in Oracle if the procedure having 1500 lines codes? Can u make a video ?

[@SivaAcademy]

Please look into this video, kzbin.info/www/bejne/fWeamJSGbJ6bqqc

[@abhisheknegi8053]

Hello please share the link regarding pragma autonomous transaction

[@SivaAcademy]

Yet to post a video, please stay tuned

[@raju26067]

Can we call or use procedure inside the function ?one example please?

[@SivaAcademy]

Yes you can call, I will post a video on this question. In the mean time, you can read my answer for your question here. www.quora.com/Is-it-possible-to-define-a-function-inside-the-procedure-definition/answer/Jeevan-139 www.quora.com/Is-it-possible-to-define-a-procedure-within-a-function-definition-and-vice-versa/answer/Jeevan-139 Thanks, Siva Jeevan

[@alokmaurya7493]

Siva Academy is Super Siva Academy ❤️

[@SivaAcademy]

Welcome :-)

[@vrushalisonawane1238]

Sir, can we call the procedure in select statment?

[@SivaAcademy]

No, you cannot call procedure in select statement however you can call via function, also please look into this kzbin.info/www/bejne/qHvYg4qsbNBgsNE

[@SivaAcademy]

No, procedure cannot be called from select statement, however you can use in with clause, and can be called via function, you can look into this kzbin.info/www/bejne/qHvYg4qsbNBgsNE

[@ramchinthakayala2092]

hi, how to display 1 to 10 numbers in a single row (horizontally). Kindly let me know??

[@SivaAcademy]

Displaying as a single string of comma separated values like 1,2,3,4,5,.... Is quite straight forward, however displaying in ten individual column is bit complex.... Please let me know your requirement

[@srikanthk.chandiran343]

Please try this SELECT LISTAGG(LEVEL, ',') WITHIN GROUP( ORDER BY LEVEL) EXPECTED_OUTPUT FROM DUAL CONNECT BY LEVEL

[@ramanhasan9398]

Thank you!!

[@SivaAcademy]

Welcome bro

[@ravipatisrikanth8331]

What is the autonomous transaction functions

[@SivaAcademy]

Please look into this video kzbin.info/www/bejne/hXyafHRvbtWfjrM kzbin.info/www/bejne/iGXHqqmoi6hsqpI

[@pritikumari5616]

please let us know that how to optimize function?

[@SivaAcademy]

Are you looking to tune PLSQL code inside function?

[@pritikumari5616]

@@SivaAcademy yes by which performance of function will fast..

[@udbhavdwivedi410]

Why do we use function when we have Procedure?

[@SivaAcademy]

procedure cannot be called directly in SQL

[@atulraut6212]

Hi Siva do you Take online Training for SQL Plsql

[@SivaAcademy]

Please drop me mail at [e-mail]

[@srikanthuyyala3811]

Please explain joins

[@SivaAcademy]

sure please stay tuned

[@bharathipandian45]

Hi siva, can you help me to crack production support interview questions

[@SivaAcademy]

Please post your questions, will get it addressed if not posted already

[@bharathipandian45]

@@SivaAcademy ok siva a help. Can you post production support related interview questions. Am a production support engineer.

[@venkatasubbareddyonteru4581]

Video is not playing

[@suyogthakare828]

Sir how can be private procedure call in the package

[@SivaAcademy]

@Suyog, Private procedure/functions are the one that is declared&defined only within the package body, These procedures are not declared in the spec, hence these private procedures cannot be accessed by anyone,. Only with in the body of the package, the other package members ( ie., ther other procedure or function) can call the private procedures. May be I will explain with an example in the next subscriber comment replay video with an example for everyone's benefit. Please stay tuned.

[@suyogthakare828]

@@SivaAcademy thank you sir it really hepful for me. I will be waiting for the video

[@sr2568]

Please bro,do video series on MACRO

[@SivaAcademy]

Yes bro, work in progress, will be added soon, please stay tuned

[@SuReshMiStry1]

High quality video not available

[@bhanupratap2921]

Voice is too low here ..otherwise videos are good

[@SivaAcademy]

Thank you