What is NOCOPY Hint | Oracle Pass by reference & Pass by Value | Will NOCOPY improve performance?

[@Hari_Ramana]

The simple yet elegant way of explaining.

[@Wilq09]

this is so far the best explanation of nocopy i've seen. thank you!

[@SivaAcademy]

Thank you

[@joseronald111]

Excellent video..and as always crystal clear explanation..thank you so much sir👍

[@SivaAcademy]

You are most welcome. Thanks for your comment bro

[@ajinkyagaikwad2387]

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 🙏🙏🙏🙏🙏

[@SivaAcademy]

My pleasure, thank you 💐🙏

[@shivanand.s.magadum6276]

Crystal clear, Thank you. You got new subscriber today.

[@SivaAcademy]

Welcome 💐

[@lunasworld1110]

Hi bro, need more videos about all types of hints. Thanks in advance.

[@SivaAcademy]

Sure 👍

[@pressi14188]

Great concept and we learn good concepts every time, thanks for your great video

[@SivaAcademy]

Welcome bro

[@vikrantmukkawar9737]

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

[@SivaAcademy]

Sure, Please stay tuned.

[@TheHarishAgarwal]

Can you rollback in the exception block to prevent passed by reference overwriting the memory location?

[@nareshkatta9740]

please make a video on oracle awr report.

[@SivaAcademy]

sure, please stay tuned

[@abifreeflowers]

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

[@SivaAcademy]

My pleasure bro 💐💐

[@jayashreejay2989]

In a interview it was asked to tell about partition

[@SivaAcademy]

Sure please stay tuned, will cover in upcoming videos

[@deepakjagdale6215]

Really nice.. with graphics more easy to understand.. Thanks sir

[@SivaAcademy]

Welcome :-) Thank you

[@thiru601mbt6]

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...

[@SivaAcademy]

Thank you, can you please eloborate your question, do you want procedure with multiple parameters?

[@letsreview6517]

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 KZbin.

[@SivaAcademy]

Thank you

[@chaitanyaas3044]

Outstanding Explanation...

[@gauravshukla6799]

Perfectly explained,In depth concept explaination,great work :)

[@SivaAcademy]

Thank you bro

[@shaikrasheed2861]

Please make performance tuning related videos sir

[@SivaAcademy]

sure, Please stay tuned

[@gunjanbhatt4543]

Hi, Please make a complete video on oracle hints

[@SivaAcademy]

sure, please stay tuned

[@shashank2004]

Awsome explanation bro👍... Very useful Ur way to explain it's unique and easier

[@shashank2004]

Thanks a lot bro to make the video and I am awaiting for your upcoming videos.

[@SivaAcademy]

Welcome bro, Please stay tuned

[@pradeepverma8277]

Thank u so much sir provide such a good and useful video.

[@SivaAcademy]

Welcome bro :-)

[@sujaa1000]

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

[@SivaAcademy]

Sure, i will cover as part of performance tuning video series, content preparation in progress. Will update you soon, please stay tuned

[@minicat1983]

Very clear explanation.

[@SivaAcademy]

Welcome

[@in2sun275]

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

[@SivaAcademy]

no, both places, its nested table only., we cannot define as nested table, and treat it like associative array

[@in2sun275]

@@SivaAcademy sorry i am confused

[@WorldEye88]

Thank you sir. Clear and very helpful explanation.

[@SivaAcademy]

You are welcome

[@rampandraju1812]

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

[@SivaAcademy]

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??

[@rampandraju1812]

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

[@rampandraju1812]

In a simple way i need to find the long running queries with the help of some script

[@SivaAcademy]

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

[@rampandraju1812]

​@@SivaAcademy​U mean to find the long run queries I definitely need to run them?

[@PankajPal-iy7kn]

parallel hint please?

[@nrini16]

Please make videos on other hints too

[@SivaAcademy]

sure, please stay tuned

[@arjunchauhan888]

AWESOME Explanation

[@SivaAcademy]

Thank you

[@nrini16]

Awesome explanation

[@SivaAcademy]

welcome :-)

[@nntrainingandplacementsolu9734]

very nice video sir with clear explanation,please share code in description section

[@SivaAcademy]

Posted, please check description

[@nntrainingandplacementsolu9734]

@@SivaAcademy thanks sir

[@kumar-bi1to]

Very helpful. Thank you

[@SivaAcademy]

Welcome bro, thank you

[@srinivass5552]

Great explanation !!

[@SivaAcademy]

Thank you!

[@Adbhutashole]

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

[@dineshkumar-vp9dm]

Pls send what is different between for clause and forall clause plsql Pls share video

[@SivaAcademy]

sure, please stay tuned

[@Adbhutashole]

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 .

[@Adbhutashole]

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

[@deepanshugaur8674]

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..

[@SivaAcademy]

sure, please stay tuned.

[@deepanshugaur8674]

@@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

[@SivaAcademy]

Hi, partially answered here, please look into this video kzbin.info/www/bejne/habPnIaIiKt9eZo

[@jayashreejay2989]

Can yu also teach about partition concept in plsql sir

[@SivaAcademy]

Sure please stay tuned

[@swapnaram8799]

Thank you so much sir

[@SivaAcademy]

Welcome

[@manjunathreddy3484]

highest salary of the dept which has largest no of employees

[@nareshkatta9740]

what is the use of awr report?

[@SivaAcademy]

sure, please stay tuned

[@shaikrasheed2861]

Are refcursor and pass by reference are same ??

[@SivaAcademy]

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

[@palanivelu.a]

Super..

[@SivaAcademy]

Thank you

[@abbeydudeuk]

dang again had to give LIKE.

[@SivaAcademy]

Thank you :-)

[@googdriveforme4115]

Very useful

[@SivaAcademy]

Glad to hear that

[@juno7893]

👏🏻👏🏻👏🏻

[@SivaAcademy]

🙏💐

[@shobharani8634]

Very nice video Siva..can you give your mail id pls

[@vijayaparasurmannu369]

Thank you for the clear explanation sir.Very helpful

[@SivaAcademy]

Welcome bro 🙏