I am back to recounting what I learned at my hand on instructor led Oracle training. The first topic deals with who is authorized to execute database objects like functions or procedures. The default method is called definer’s rights. This means that the rights of the user who created (compiled) the function or procedure is used when determining what the code can do. This authorization is checked at the function and procedure level.On a totally different topic, you can provide Oracle with a NOCOPY hint when specifying parameters to a function or procedure. This is truly a hint that Oracle may end up ignoring. This hint implies that the developer recommends the parameters be passed by reference. The benefit is a speed increase especially when the parameters are large. However there is a danger when using this. If your program blows up in the procedure or function, the state of your data may be undefined.
Finally I want to briefly mention the BULK COLLECT mechanism. I have seen top notch Oracle developers use this feature. It has always mystified me. Perhaps it has something to do with how the words BULK COLLECT sound. Anyway this technique is used when exchanging data between the database and a collection.
Normally you would iterate through a collection one record at a time and exchange the data with the database. This however is a slow technique as you are switching context between the PL/SQL and SQL governors. The BULK COLLECT let’s you do all the work at once. It is good when you are dealing with a lot of data (a big table). It is also easier to code than to manually iterate through the records yourself.
