The explanation is really amazing and too much usefull ..Wants to learn in depth of performance tuning ,partition , algorithm of join in explan plan ,hints , optimiser ...Wants to know how it's happening internally ..
Lv_namelist(lv_namelist.last() )= i... Can anyone explain this line? I know lv_namelist is a collection variable and lv_namelist.last means last value. But I am not able to gather the logic here... Please explain
@Shubhra Pandey, Sure, I am planning to start a new series on PLSQL and SQL performance tuning concepts. The scope of tuning concepts and methods are large., I will try my level best to make the learning easy and effective as much as possible. Stay tuned for tuning related videos... Thanks , Siva.
What if I use into keyword in select statement to store value of function return and use variables to get out parameter values ? Declare Outvar number; Retvar number; Begin Select fnsqcu(2,outvar) into retvar from dual; End Will this work ?
Hi sir can u tell me can we call the dml & ddl statement inside the functions if yes then how & why it's not a gud practice to call dml inside function. Also share the diff between function & procedure ??
Hi Majon, yes you can call, already posted on the same question, Please look into below mentioned videos th-cam.com/video/yyRM-p2xfZc/w-d-xo.html th-cam.com/video/CkkIbsi_3cc/w-d-xo.html
Sir i have a question related to sql please consider a scenario:- I have a table of employees working in different departments say emplyee E1 works in dept A,B,C,D,E,F E2 works in C,D,E,F E3 works in A,B,C Expected output is :- Return the details of employees only if that employee works in dept C and B For more clearity:- Since E1 works in dept C and B as one of their departments the query should give all the details of E1 including his other department details. Similarly E2 works in C dept but not in B so E2 data should not be returned. E3 works in both depts B and C so all his dept details should be returned.
Yes, you can call procedure from function, also you can have DML statements in function also, however there are few restrictions, please look into this video th-cam.com/video/yyRM-p2xfZc/w-d-xo.html
@Karthik, With clause is available from previous releases of oracle itself, however the "FUNCTION" support as part of with clause is available from 12c only, you can look into this video for more information about 12c with clause feature, th-cam.com/video/rJuRWv7k-zo/w-d-xo.html Thanks, Siva
Can we pass the result sets as a collection to the java/dot net application from a function? Generally we will do it using sys_refcursor, If yes what is the best way among the two and what is the difference bw two methods?
Hello Siva,Thanks for all your videos.I have one confusion if possible can you please clear that? While explaining about return statement you return collection variable via return statement ,I did not understand the use of extend and last method there ?
Sir If we want to prepare the interview questions means your website have a 33 questions that is enough or either we want to study the 70 questions!!!!
WE WANT TO GENERATE SEQUENCE NUMBERS USING STORED FUNCTION AND WE CALL FUNCTION IN INSERT STATEMENT IT WILL AUTOGENERATE THE UNIQUE IDS PLZ DO A VIDEO ON THIS CONCEPT
![ออร่า (AURA) - JUEPAK feat. SARAN | [OFFICIAL MV]](http://i.ytimg.com/vi/RY9j7Lzp-FI/mqdefault.jpg)
You are simply amazing. Your communication is also awesome
It's my pleasure,Thank you so much :-)
Very useful yet dense topic my friend. I like more the part where you show the collection and ref cursor use. That is clever. Thank you.
Welcome 🙏🙏
what a explanation
Siva Academy just phenomenal....
🙏🙏🙏🙏
Super job ..very detailed explanations thanks Siva keep the knowledge flow going on ...I learn something new 😀
The explanation is really amazing and too much usefull ..Wants to learn in depth of performance tuning ,partition , algorithm of join in explan plan ,hints , optimiser ...Wants to know how it's happening internally ..
Sure, please stay tuned for more in depth videos
Lv_namelist(lv_namelist.last() )= i... Can anyone explain this line?
I know lv_namelist is a collection variable and lv_namelist.last means last value. But I am not able to gather the logic here... Please explain
lots of love hats off to you
🙏🙏🙏💐Welcome bro
hi can we pass a parameters to running program in plsql? is it possible.
there is a 1 single record in a table but i want to populate the data 10 times. Please answer
Best teaching method with live examples
Thanks bro 🙏
Great job...@
Your inputs are making us more confidents to learn more. Once again thanks
My pleasure bro
Knowledge is DEVINE... 💐
🙏🙏🙏
Thanks Siva for e.g. of INOUT parameter in function .
Welcome bro 🙏
great work sir. Really informative.
Thanks Shubhra Pandey :-)
@@SivaAcademy Can we have a concise and clear vdo like this one about Performance tuning.
@Shubhra Pandey, Sure, I am planning to start a new series on PLSQL and SQL performance tuning concepts. The scope of tuning concepts and methods are large., I will try my level best to make the learning easy and effective as much as possible. Stay tuned for tuning related videos...
Thanks , Siva.
Great job Siva!👍👍Learned something new today. Thank you.
My pleasure bro
Really excellent explanation. Super Sir. Thanks so much
Welcome bro
Sir can we multiple records using functions using record data type, and can bulk collect used with records?
Can you please explain about explain plan in query execution.
Sure please stay tuned for performing tuning videos, will explain there
What if I use into keyword in select statement to store value of function return and use variables to get out parameter values ?
Declare
Outvar number;
Retvar number;
Begin
Select fnsqcu(2,outvar) into retvar from dual;
End
Will this work ?
He covered this question @ 3:05.. Have a look bro.
Excellent explanation that helps lot and easy to understand
Welcome
Thanks for your class about function!
What is table function or Pipeline function
Most of the interviews they ask about these function
sure, please stay tuned, its already part of my upcoming list, will try to post soon.
Thanks for your reply!
I am waiting for your video..
Please explain where we can use this function in Realtime....
Thank you for this session; it was very informative.
Welcome bro
Hi sir can u tell me can we call the dml & ddl statement inside the functions if yes then how & why it's not a gud practice to call dml inside function.
Also share the diff between function & procedure ??
Hi Majon, yes you can call, already posted on the same question, Please look into below mentioned videos
th-cam.com/video/yyRM-p2xfZc/w-d-xo.html
th-cam.com/video/CkkIbsi_3cc/w-d-xo.html
Can you please provide the details of other sys objects like ODCIVARCHAR2LIST that can be used in for storing data temporarily.
Siva, need unix and sql plsqk production support interview questions
how to add primary key constraint for a column which has already duplicate values?
Please look into this video th-cam.com/video/SFlmUk9IUg8/w-d-xo.html
please make a video on to create a function to take a number and display its square using inout ...
wonderful explanation
why we can't call any function with OUT/INOUT parameter from select statements??
Can you please make videos on performance tuning interview questions
@Vilas, Thanks for your comment, Sure, Please stay tuned for performance related videos, will be posting soon.
Sir a video on oracle architecture please would be appreciated......mostly asked question in interviews....u explain so nicely....really helped me
thankyou for examples .. worth watching
Thank you
Hi sir, please provide oracle installation link and installation process video
Sir i have a question related to sql please consider a scenario:-
I have a table of employees working in different departments say emplyee E1 works in dept A,B,C,D,E,F
E2 works in C,D,E,F E3 works in A,B,C
Expected output is :-
Return the details of employees only if that employee works in dept C and B
For more clearity:-
Since E1 works in dept C and B as one of their departments the query should give all the details of E1 including his other department details.
Similarly E2 works in C dept but not in B so E2 data should not be returned.
E3 works in both depts B and C so all his dept details should be returned.
Hi Shubhan, Whats the structure of table data? can you give me the sample data in row wise format?
Sample data:-
Emp_id Emp_name Dept_no Joining_date
1 , scott,A,10-may-2018
1,scott,B,20-June-2018
1, scott,C,20-August-2019
1,scott,D,19-June-2020
2,tiger,C,12-May-2018
2,tiger,B,10-January-2017
2,tiger,D,19-September-2019
2,tiger,E,13-March-2019
2,tiger,F,10-October-2020
3,Sam,A,19-January-2016
3,Sam,C,09-October-2018
3,Sam,B,17-December-2019
3,Sam,D,01-June-2019
4, John,A,10-may-2018
4, John,E,31-March-2019
4, John,F,16-may-2016
4, John,G,12-March-2018
if the employee works in departments B and C, return all the department details related to that employee.
Expected output:-
1, scott,A,10-may-2018
1,scott,B,20-June-2018
1, scott,C,20-August-2019
1,scott,D,19-June-2020
2,tiger,C,12-May-2018
2,tiger,B,10-January-2017
2,tiger,D,19-September-2019
2,tiger,E,13-March-2019
2,tiger,F,10-October-2020
3,Sam,A,19-January-2016
3,Sam,C,09-October-2018
3,Sam,B,17-December-2019
3,Sam,D,01-June-2019
can we have multiple out parameters in a function sir??
yes, you can have multiple out and INOUT parameters too
then why we use procedure,when both proc and func gives multiple values?
Though function return/OUT more than one more than one values, there are restrictions using such function sql.
siva sir ... can we call function into procedure
Yes Ganesh
1.can we call a procedure inside a function? 2.can function have DML statements?
Yes, you can call procedure from function, also you can have DML statements in function also, however there are few restrictions, please look into this video
th-cam.com/video/yyRM-p2xfZc/w-d-xo.html
Thank for your sharing.
Welcome
Does with clause support 12c only??
@Karthik, With clause is available from previous releases of oracle itself, however the "FUNCTION" support as part of with clause is available from 12c only, you can look into this video for more information about 12c with clause feature,
th-cam.com/video/rJuRWv7k-zo/w-d-xo.html
Thanks,
Siva
Can we pass the result sets as a collection to the java/dot net application from a function?
Generally we will do it using sys_refcursor,
If yes what is the best way among the two and what is the difference bw two methods?
@Mahendra, yes, you can pass the collection to java, will post a separate video on how to pass as parameter.
Siva Academy
If yes which is the best way sys_refcursor or collection and why
Hi sir,
Can you make the video for UTL_FILE Concept in Plsql.
Sure, please stay tuned
find out the 3rd highest salary from table for each years fallowed by each department
Thanks Siva ..Nicely explained
Welcome bro 🙏
Hello Siva,Thanks for all your videos.I have one confusion if possible can you please clear that?
While explaining about return statement you return collection variable via return statement ,I did not understand the use of extend and last method there ?
Extend method is to allocate memory, and last method to get the subscript of last element
Thanks, you are doing good job.
Thank you 🙏
Hello sir , thanks you for making pl SQL learning videos it's really help. The please work on audio quality of video. Thank you so much.
@Amita, thanks for your comment, yeah in recent videos I have corrected little, please let me know if it's quality is still not good
@@SivaAcademy The quality is good now😊
Hi siva, what is type? Why we use it for?
Good examples.
Thank you
Just awesome
hi shiva can I get yours videos on regular expressions
Hi Gautam, I am yet to post on regular expression, please stay tuned
@@SivaAcademy please do it fast sir because waiting from long time.
Sure bro 👍
Excellent Sir...
Thank you
Sir If we want to prepare the interview questions means your website have a 33 questions that is enough or either we want to study the 70 questions!!!!
Hi Shankar, all questions are important, I just updated the link, thanks for letting me know.
Excellent!!
You are awesome.
Thank you so much :-)
I understood only key learning from this video. 😅
WE WANT TO GENERATE SEQUENCE NUMBERS USING STORED FUNCTION AND WE CALL FUNCTION IN INSERT STATEMENT IT WILL AUTOGENERATE THE UNIQUE IDS PLZ DO A VIDEO ON THIS CONCEPT
Great , Thanks :)
Thank you
PLS-00382: expression is of wrong type
Plzz increase ur voice in all vdos.. Plz explain loudly..
Yes bro, hopefully recent videos are better
SQL query tuning step by step sir
@kathir, sure. Will post as part of tuning video series
Nice
Thanks Manoj