Really you are simply amazing ,,,, I tried to understand what is no copy since last 4 years ,,but today I got everything about No copy ,,, God bless you ,,, You are great 🙏🙏🙏🙏🙏
really awesome video series with neat and clear explanation..my fingers are looking for one or the other videos of urs ,making me sit all time infront of my lappy
Thanks for all your videos. I never miss your videos. In the current topic can please share the video with multiple parameters. It will helpmany of them who are beginner. Please sir kindly requesting one more video in this topic...
Few things knew about NOCOPY after doing practice. 1) If exception occurred in main procedure and it can not be handle & formal parameters(in out or out) defined without NOCOPY. Then value will not change for calling environment. 2)If exception occurred in main procedure and it handled & formal parameters(in out or out) defined without NOCOPY. Then value will change for calling environment. 3)If exception occurred in main procedure and it can not be handle & formal parameters(in out or out) defined with NOCOPY. Then value will get change for calling environment. 4) If exception occurred in main procedure and it handled but in exception section if it re-raised & formal parameters(in out or out) defined without NOCOPY. Then value will not change for calling environment NOCOPY is used to change the behaviour of call by value parameter to call by reference
Hi Siva, thank you again, I have a request, can you please make a video on how oracle arrives at an excecution plan.....CBO or RBO, on what basis Oracle decides to use CBO or RBO, how oracle or the optimizer process a query for the beginning.......to the end. Look forwards to hear from you. Regards, Sujaa
inside package , u mentioned nested table but u did associative array inside loop . How does it executed i dont know( index by datatype should be there right).But nocopy hint u explained very well .Thank you so much
I do have a big doubt from long ago and no one clarified me yet.....Hope u do......In a set of 100 queries for example can we get the tables from all the query which is blocking that query because of some issue issue with table as table getting locked..improper use of indexes etc......its like can we get names of those tables which is blocking or making a query execute for long time
Can you please elaborate your question little bit more, is all the 100 statements are select statement only? Or is there any update statement that prevents others session to do dml operation? Or is the question about finding the long running queries??
first of all thank you for the quick reply.My question is i have a set of 100 select queries just select count(*) queries and the job i need to do is to execute all those queries and dump the output count in result table..I created a procedure to fetch count from those 100 queries....but there are 2 to 3 queries out of 100 that gets executed continuously because of the problem with tables in that query (creating false index,table getting locked etc)......how i can identify those queries which will take longer time prior with the help of script so that i will stop those queries from entering into procedure to execute
Finding the long running queries is easy, but finding the underlying reason immeditly may not be straight forward... i will further test some real time examples, and reply with relevent informations, and will post a video with practical sqls. quick answer.to finding the locked objects we can use the "v$locked_object" view, however to find the long running queries, we can check on the v$session_longops
Hi, Your video is very helpful. Thanks!! Can u please explain the difference between cluster and non cluster index. And between primary and unique index..
Doesn't matter how much u have practiced on PLSQL how much u provide conceptually right answer in the interview but but if u could not give RIGHT SQL output of NORTH INDIAN INTERVIEWER(specially noida Gurgaon) u will be never selected. Had given interview in 20 mnc all they have same SQL query interview question .
Sapient Interviewer asked 1.5 years experience candidate: how will u reschedule failed procedure ? Even this question don't know 7 years experienced TCS and Oracle guys .. Fidelity investments interviewer answer conflict : Select 1,2 ,3 from.dual Union Select 1,2from dual ; Ujjwal can u give me o/p .. candidate: we can't join it directly ...but if we place null as an thrid attribute in second query then it will give the o/p .. Interviewer said : no u r wrong ... Maruti india Gurgaon interview anwer conflict : How many types of triggers there? Candidate : dml ,ddl ,mutating trigger. Interviewer: thr is no such trigger named mutating... Candidate : pls check it at ur end it is trigger and also error
Conceptually same, but functionally different. ref cursor will point to the memory, and get fetched by the calling program, however pass by reference load all the data into the memory
The simple yet elegant way of explaining.
this is so far the best explanation of nocopy i've seen. thank you!
Thank you
Excellent video..and as always crystal clear explanation..thank you so much sir👍
You are most welcome. Thanks for your comment bro
Crystal clear, Thank you.
You got new subscriber today.
Welcome 💐
Really you are simply amazing ,,,, I tried to understand what is no copy since last 4 years ,,but today I got everything about No copy ,,, God bless you ,,, You are great 🙏🙏🙏🙏🙏
My pleasure, thank you 💐🙏
Great concept and we learn good concepts every time, thanks for your great video
Welcome bro
Really nice.. with graphics more easy to understand.. Thanks sir
Welcome :-) Thank you
Outstanding Explanation...
Hi bro, need more videos about all types of hints. Thanks in advance.
Sure 👍
really awesome video series with neat and clear explanation..my fingers are looking for one or the other videos of urs ,making me sit all time infront of my lappy
My pleasure bro 💐💐
Can you rollback in the exception block to prevent passed by reference overwriting the memory location?
Thanks for all your videos. I never miss your videos. In the current topic can please share the video with multiple parameters. It will helpmany of them who are beginner. Please sir kindly requesting one more video in this topic...
Thank you, can you please eloborate your question, do you want procedure with multiple parameters?
The way of explanation is excellent can you please make series on Oracle from beginner level to expert level it will very helpful for all of us
Sure, Please stay tuned.
please make a video on oracle awr report.
sure, please stay tuned
Awsome explanation bro👍... Very useful
Ur way to explain it's unique and easier
Thanks a lot bro to make the video and I am awaiting for your upcoming videos.
Welcome bro, Please stay tuned
In a interview it was asked to tell about partition
Sure please stay tuned, will cover in upcoming videos
Thank u so much sir provide such a good and useful video.
Welcome bro :-)
If I 've any doubt on concept I always look for your video, hoping that there must be a solution or explanation video available in RUclips.
Thank you
Perfectly explained,In depth concept explaination,great work :)
Thank you bro
Very clear explanation.
Welcome
Thank you sir. Clear and very helpful explanation.
You are welcome
Few things knew about NOCOPY after doing practice.
1) If exception occurred in main procedure and it can not be handle & formal parameters(in out or out) defined without NOCOPY.
Then value will not change for calling environment.
2)If exception occurred in main procedure and it handled & formal parameters(in out or out) defined without NOCOPY.
Then value will change for calling environment.
3)If exception occurred in main procedure and it can not be handle & formal parameters(in out or out) defined with NOCOPY.
Then value will get change for calling environment.
4) If exception occurred in main procedure and it handled but in exception section if it re-raised & formal parameters(in out or out) defined without NOCOPY.
Then value will not change for calling environment
NOCOPY is used to change the behaviour of call by value parameter to call by reference
Great explanation !!
Thank you!
AWESOME Explanation
Thank you
Awesome explanation
welcome :-)
Hi,
Please make a complete video on oracle hints
sure, please stay tuned
Hi Siva, thank you again, I have a request, can you please make a video on how oracle arrives at an excecution plan.....CBO or RBO, on what basis Oracle decides to use CBO or RBO, how oracle or the optimizer process a query for the beginning.......to the end.
Look forwards to hear from you.
Regards,
Sujaa
Sure, i will cover as part of performance tuning video series, content preparation in progress. Will update you soon, please stay tuned
Very helpful. Thank you
Welcome bro, thank you
inside package , u mentioned nested table but u did associative array inside loop . How does it executed i dont know( index by datatype should be there right).But nocopy hint u explained very well .Thank you so much
no, both places, its nested table only., we cannot define as nested table, and treat it like associative array
@@SivaAcademy sorry i am confused
Please make performance tuning related videos sir
sure, Please stay tuned
highest salary of the dept which has largest no of employees
I do have a big doubt from long ago and no one clarified me yet.....Hope u do......In a set of 100 queries for example can we get the tables from all the query which is blocking that query because of some issue issue with table as table getting locked..improper use of indexes etc......its like can we get names of those tables which is blocking or making a query execute for long time
Can you please elaborate your question little bit more, is all the 100 statements are select statement only? Or is there any update statement that prevents others session to do dml operation? Or is the question about finding the long running queries??
first of all thank you for the quick reply.My question is i have a set of 100 select queries just select count(*) queries and the job i need to do is to execute all those queries and dump the output count in result table..I created a procedure to fetch count from those 100 queries....but there are 2 to 3 queries out of 100 that gets executed continuously because of the problem with tables in that query (creating false index,table getting locked etc)......how i can identify those queries which will take longer time prior with the help of script so that i will stop those queries from entering into procedure to execute
In a simple way i need to find the long running queries with the help of some script
Finding the long running queries is easy, but finding the underlying reason immeditly may not be straight forward... i will further test some real time examples, and reply with relevent informations, and will post a video with practical sqls.
quick answer.to finding the locked objects we can use the "v$locked_object" view, however to find the long running queries, we can check on the v$session_longops
@@SivaAcademyU mean to find the long run queries I definitely need to run them?
parallel hint please?
very nice video sir with clear explanation,please share code in description section
Posted, please check description
@@SivaAcademy thanks sir
Thank you so much sir
Welcome
Please make videos on other hints too
sure, please stay tuned
Hi,
Your video is very helpful. Thanks!!
Can u please explain the difference between cluster and non cluster index.
And between primary and unique index..
sure, please stay tuned.
@@SivaAcademy thanks,
Please also explain how many null values we can have in unique key and why we have only one primary key per table
Hi, partially answered here, please look into this video
ruclips.net/video/OulkSRSFJC8/видео.html
Super..
Thank you
Pls send what is different between for clause and forall clause plsql
Pls share video
sure, please stay tuned
Doesn't matter how much u have practiced on PLSQL how much u provide conceptually right answer in the interview but but if u could not give RIGHT SQL output of NORTH INDIAN INTERVIEWER(specially noida Gurgaon) u will be never selected.
Had given interview in 20 mnc all they have same SQL query interview question .
Sapient Interviewer asked 1.5 years experience candidate: how will u reschedule failed procedure ?
Even this question don't know 7 years experienced TCS and Oracle guys ..
Fidelity investments interviewer answer conflict :
Select 1,2 ,3 from.dual
Union
Select 1,2from dual ;
Ujjwal can u give me o/p .. candidate: we can't join it directly ...but if we place null as an thrid attribute in second query then it will give the o/p ..
Interviewer said : no u r wrong ...
Maruti india Gurgaon interview anwer conflict :
How many types of triggers there?
Candidate : dml ,ddl ,mutating trigger.
Interviewer: thr is no such trigger named mutating...
Candidate : pls check it at ur end it is trigger and also error
what is the use of awr report?
sure, please stay tuned
dang again had to give LIKE.
Thank you :-)
Very useful
Glad to hear that
Can yu also teach about partition concept in plsql sir
Sure please stay tuned
Are refcursor and pass by reference are same ??
Conceptually same, but functionally different. ref cursor will point to the memory, and get fetched by the calling program, however pass by reference load all the data into the memory
👏🏻👏🏻👏🏻
🙏💐
Very nice video Siva..can you give your mail id pls
Thank you for the clear explanation sir.Very helpful
Welcome bro 🙏