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?

I don't know AWS Aurora Postgres in detail, but it should be Postgres with different storage. If Aurora Postgres has UTL_FILE, then very probably it is from preinstalled Orafce extension. — Pavel Stehule
– Pavel Stehule, Commented Jul 21, 2020 at 7:42

Pavel Stehule · Accepted Answer · 2020-07-21 18:43:31Z

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".

edited Jul 21, 2020 at 18:43

answered Jul 21, 2020 at 7:59

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle UTL_FILE exceptions alternatives in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related