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
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
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
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?
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.
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;
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
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.
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
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 ?
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)
No, you cannot call procedure in select statement however you can call via function, also please look into this th-cam.com/video/rJuRWv7k-zo/w-d-xo.html
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 th-cam.com/video/rJuRWv7k-zo/w-d-xo.html
@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
Welcome, yes, you can have, however there are few restrictions, for more details please look into this video th-cam.com/video/yyRM-p2xfZc/w-d-xo.html th-cam.com/video/CGLWDytoUeE/w-d-xo.html
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
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
@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.
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
All the videos from Siva Academy are super.
Welcome bro 🙏
@@SivaAcademy can you please explain difference between execution plan and explain plan
Sure, please stay tuned
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
Great, Thanks
Very Good and Helpful Information Also Your teaching style is very good Sir
Thank you!!! :-)
very helpful information hatsoff sir
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
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
ok... now u can go to hell
Great job please continue awesome videos sir
Welcome 🙏💐
Great Siva. Thank you for your videos. Very clear explanation.
You are welcome
All your videos are very nice easy to understand and having sense.
Welcome bro 🙏
Very helpful..thanks for amazing video
Welcome, thank you
Thank you so much for the video and sharing knowledge.....
Welcome :-) Akshay
I'm finding your videos very helpful. Thanks.
My pleasure, welcome
It's wonderful.. Nicely explaining everything... Thankuu
Welcome 😊
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?
3:47 - Start
6:00
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.
Sure Akash, Please stay tuned.
I have gone this question many time in first technical round
Nice explanation. I have 12plus years experience. But they asked this question.
yeah, this happens sometimes :-)
Please make video on role for Oracle
very clear post , thanks siva.
Welcome 🙂 🙏
Very helpful Siva,🙏 many thanks
Welcome Vijay 💐💐
So unique and insightful
Thank you
Can you please explain difference between execution plan and explain plan
Which book is better for reading for sql plsql.
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;
Sir ...why we go for functions instead of a procedure ...can u pls explain this !?
Hi Siva,
Where can I get the scripts used in all these videos?
Thank you.
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
Please look into this video th-cam.com/video/yyRM-p2xfZc/w-d-xo.html
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?
Procedure cannot be called in select Statement. However function can be used in select Statement.
Thank you sir ...you are awesome
It's my pleasure,Thank you so much :-)
Please also make a video on pipelined and parallel functions...
Sure please stay tuned 👍
Autonomous transaction function, can you show us some examples
Primary key does not have null and duplicates but foreign key does have why?
Can you please explain 4 th point with example in case of function?
Pls post video related procedure and function in with clause
Its already available, please look into this video th-cam.com/video/rJuRWv7k-zo/w-d-xo.html
Dear Siva, thanks for posting such valuable concepts.
Can we have out parameters in function?
Yes you can, please look into this video th-cam.com/video/PyJ5Zwfb7WY/w-d-xo.html
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.
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
Hi,
Could you pl share a video on the similarities or difference between Loops and Cursors in PL/SQL?
Sure please stay tuned
Siva, please create a detailed video on debugging
Sure please stay tuned 👍
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 ?
Will post a video on advantages of writing with in packages soon, please stay tuned
@@SivaAcademy Thank you very much for your response Siva. Please add the link once posted
Please look into this video
th-cam.com/video/Mje8pEtVe5I/w-d-xo.html
@@SivaAcademy Thank you so much for your effort and timely response. Let me watch.
Welcome 👍💐
Sir returning keyword in the procedure is not working for me ,,I am using 19 version of Oracle plsql
Can we call procedure inside a function and calling function inside a procedure?
Yes you can call both way, however there are few limitations, will explain with example soon, please stay tuned
Fun fact. The creator is actually from Decatur, Georgia. He has to use this accent to get views.........just kidding. Great vid. Thanks alot.
😀😀 thanks for your comments
SQL ful ah step step ah Tamil iruka sir unka vidro
Can we fetch the last 3 records inserted without having an identity column nor date column ?
Yes u can
select top(select count(*) from tabl) from tab1
minus
select top(select count(*) from tab1)-3 from tab1;
Hi Siva do you Take online Training for SQL Plsql
Please drop me mail at siva.k.academy@gmail.com
Please explain about returning multiple values from a function
Already posted, please look into this video th-cam.com/video/PyJ5Zwfb7WY/w-d-xo.html
return refcursor
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)
Sir, can we call the procedure in select statment?
No, you cannot call procedure in select statement however you can call via function, also please look into this th-cam.com/video/rJuRWv7k-zo/w-d-xo.html
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 th-cam.com/video/rJuRWv7k-zo/w-d-xo.html
How to optimize a procedure in Oracle if the procedure having 1500 lines codes? Can u make a video ?
Please look into this video,
th-cam.com/video/G67gaP79htE/w-d-xo.html
Is plsql pass by value,pass by reference ??
For example java is allowing pass by value??
@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
Thank you sir
Very nice explanations ,Please add some extra volume.
@m Lipina, Thanks for your comments, In the recent videos volume has been raised. I will take care in upcoming videos.
Regards,Siva.
Hello please share the link regarding pragma autonomous transaction
Yet to post a video, please stay tuned
Thank you for the video..very useful...But the audio is very low....
Welcome, I hope recent videos are fine
Thank you for this nice video.
I have one query, Can funtion contains DDL statement?
Welcome, yes, you can have, however there are few restrictions, for more details please look into this video
th-cam.com/video/yyRM-p2xfZc/w-d-xo.html
th-cam.com/video/CGLWDytoUeE/w-d-xo.html
Can we call or use procedure inside the function ?one example please?
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
Can we use function inside stored procedure? pls share video on this bro
Yes, you can use function within procedure
Can u please tell what is level and connect by
Sure, please stay tuned
How can we add amount from last rows and give total output in current row?
Please post sample data and expected output
Siva Academy is Super Siva Academy ❤️
Welcome :-)
Sir, why we are using function. Because all operation should be done with procedure. But why using function???????
Pls tell me bro
Well explained
Super explaination
Thank you
Could you please explain the high level water mark topic bro
Sure bro, please stay tuned
hi,
how to display 1 to 10 numbers in a single row (horizontally). Kindly let me know??
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
Please try this
SELECT LISTAGG(LEVEL, ',') WITHIN
GROUP(
ORDER BY LEVEL) EXPECTED_OUTPUT
FROM DUAL
CONNECT BY LEVEL
please let us know that how to optimize function?
Are you looking to tune PLSQL code inside function?
@@SivaAcademy yes by which performance of function will fast..
Helpful sir
Thank you
Nice videos air
Thank you
What is the autonomous transaction functions
Please look into this video
th-cam.com/video/OK7KA99plXQ/w-d-xo.html
th-cam.com/video/R4dyvrVC9t0/w-d-xo.html
Sir how can be private procedure call in the package
@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.
@@SivaAcademy thank you sir it really hepful for me. I will be waiting for the video
Super explanation sir
Thank you
Why do we use function when we have Procedure?
procedure cannot be called directly in SQL
Please explain joins
sure please stay tuned
thank u sivagaru
Welcome 💐
Video is not playing
High quality video not available
nice video
Thanks
Thank you!!
Welcome bro
Please bro,do video series on MACRO
Yes bro, work in progress, will be added soon, please stay tuned
Thanks Siva..
Welcome George
Hi siva, can you help me to crack production support interview questions
Please post your questions, will get it addressed if not posted already
@@SivaAcademy ok siva a help. Can you post production support related interview questions. Am a production support engineer.
Sir Tamil video iruka
As of now illa bro
@@SivaAcademy Tamil irundha use ful ah irukum
Hmm OK bro, let me try
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
Voice is too low here ..otherwise videos are good
Thank you