Could anybody explain what is the difference between cursor and collection in Oracle plsql ??

Thnks sir, but one of my doubt is where exactly a Frontend(UI) able to get the required data.

Or, do you even need a procedure/function at all?  If it can be accomplished in a single query, why not have the front end run that query directly?  Cut out all the extra work of having a procedure run to get the same result.

Well, in my experience the developer that would write the front end code is the one that would write the stored procedure and it would be just as silly no matter which side the code is on.  And I can control their privileges just as much without having to call a procedure.

I seriously had a developer say that all queries need to be in one line stored procedures that return refcursors.  The explanation?  Because they could lose the front end code and then they wouldn't be able to change the query if they had to.  That was their real reason.  Seriously.  I'm not making that up.

>>And I can control their privileges just as much without having to call a procedure.

Not really.  Execute on a procedure and insert/update and delete on a table are very different.

If they have delete ability on a table and they have a SQL injection vulnerability, all your data can disappear.

If they have select and a security hole in the platform, all your data can be compromised.

Granted, stored procedures won't solve either of those problems by itself but it does help to mitigate them.

A lot also depends on the front end.  Would you have a public facing website directly access your back end database?

>>Because they could lose the front end code and then they wouldn't be able to change the query if they had to

lol... that is FUNNY!!!  See, developers do silly things!!!!!!

Thank you sir for your kind of intelligible answer. I oblised for that