I'm assuming you want to know about advantages/disadvantages between putting all the code on the client-side, or putting SQL code server-side in stored procedures.
Well, I don't see any drawback to using stored procedures.
Among other advantages :
- They are on the server, so they are easier to tune (they can be tuned apart from the client) and closer to the data.
- PL/SQL cursor cache mechanism is the best one (works with static SQL, even without bind variables, while on client-side apps you ABSOLUTELY NEED BIND VARIABLES - BTW you still need bind variables in calls to stored procs). PLSQL has been made to do SQL.
- By executing several queries in the same stored procedure you avoid several round trips between client and server.
For more precisions, you can have a look at the AskTom website and do a search.
Maybe one drawback would be for manipulating os files, because PLSQL is not that efficient at doing so.