А зачем нужно pl/psql ?

ИМХО, имеет смысл писать мелкие низкоуровневые триггеры для задачь типа «перед инзертом узнать партицию и перенаправить туда», «ведение истории изменений таблицы» и т.д. Когда логику сайта засовывают в БД абсолютно всегда получаются неоперабельные высеры, которые проще с нуля переписать, чем искать в них ошибку.

В чем профит использования тогда если получается такое г..но

Это Oracle головного мозга.

Когда-то это был стандартный способ работы с HTTP из-под ORACLE. Сейчас для этого лучше использовать APEX.

можно pl/sql генерить из нормального языка и грузить в базу автоматически при загрузке/миграции на новую схему

а еще лучше не работать с http из-под оракла

SI, прежде чем хаить разберись как оно пишется.