Oracle UTL_FILE exceptions alternatives in Postgres

Question

I'm migrating an Oracle stored procedure to AWS Aurora Postgres which has UTL_FILE operations to read and write in external files. In the Exception part, I have all these exception types used. UTL_FILE.invalid_path, UTL_FILE.invalid_operation, UTL_FILE.invalid_mode, UTL_FILE.read_error, UTL_FILE.write_error, and WHEN OTHERS.

Is it possible to handle all these exceptions in Postgres or should I go for orafce extension?

Answer 1

What I know, The AWS Aurora Postgres uses Orafce too

https://aws.amazon.com/about-aws/whats-new/2020/06/amazon-aurora-supports-postgresql-versions-117-1012-and-9617-and-adds-global-database-for-postgresql-117/

Postgres has not custom named exceptions like Oracle, so you have to rewrite your code little bit, you have to check error message instead - for example - Oracle's UTL_FILE.write_error is a Postgres's RAISE exception (named raise_exception - P0001) with error message (available in SQLERRM variable or via GET STACKED DIAGNOSTICS) is "UTL_FILE_WRITE_ERROR".