0

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?

Mano
  • 601
  • 10
  • 32
  • 1
    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 Jul 21 '20 at 07:42

1 Answers1

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

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94