тоже недавно только перешел на чистыпл скул и тоже озадачился логированием.. Вроде , вопрос простейший, но после удобнейших апи в спец системе не так-то просто привыкнуть :)

нашел несколько способов :
1) ну во первых всеми любимая директива, которая прекращает выполнение транзакции и откатывает изменения - это dbms_standard.raise_application_error. Дошли до места, поругались и все
2) если работаете не из клиентского приложения какого-либо, а отлаживаете свою процедурку в девелопере или другом инструменте разработки- можно использовать dbms_output.put_line
(в случае, если отладка идет одновременно в разных сессиях (например, различные джобы запускаете)- нужно писать в именованный канал, не помню честно сейчас на вскидку пакет, процедура put_line_pipe вроде, поищите в инете)
3) можно сделать специальную табличку для логирования, в которую записывать логи. Обычно такая табличка у всех имеется и апи к ней. Как правило, это некие процедурки, которые смотрят наличие признака разрешения отлаживать (передается некий код , наличие которого смотрится в каком-нить справочнике разрешенных отладок, и если он есть - лог в табличку пишется )
4) специальные средства трассировок и отладок в средствах разработки. но я их пока не очень умею

ааа, со стороны dba ?

ой...

трассировки, так же можно включить журналирование изменения объектов оракловыми инструментами, но я не знаю , надо искать