Зачем работать с базой ORACLE только через процедуры?
Если проект простой, то конечно удобнее из клиентского приложения вызывать SELECT, UPDATE и  INSERT.
Но как только проект становится достаточно большим, или накладываются дополнительные ограничения на безопасность, то удобнее вынести часть бизнес-логики на сервер. В данном случае - на хранимые процедуры.
Плюсы следующие:
1. Хранимые процедуры и клиентское приложения могут писать разные люди (команды), с разной подготовкой. Хранимые процедуры - боле опытные, клиентское приложение - более неопытные.
2. Хранимые процедуры могут обеспечить дополнительный уровень безопасности. Часть проверочной логики может быть реализовано на сервере. И клиентское ПО даже изменив или подделав вызовы не получит не принадлежащие им данные. Если вся логика приложения реализована на клиенте, то злоумышленник может переписать запросы и получить не принадлежащие ему данные. Помимо этого от клиентского ПО скрывается структура БД.
3. Хранимые процедуры, как неинтерактивные модули, проще отлаживать и тестировать в автоматическом режиме. 
4. Написанные хранимки меньше, чем клиентское ПО, подвержены изменениям в ходе эволюции и активно повторно используются в разных клиентских модулях.
Встречал периодически в некоторых фримах. В основном если запросы очень сложные, с подзапросами и некоторой логикой, то их выносят в процедуры, в остальных случаях это оверхэд. Дебажить их то еще удовольствие. Большие проблемы с вложенными транзакциями из вне. Поэтому рассматриваю их больше как неизбежное зло.