0

I have a source code querying an Oracle database.

Is it possible to replace the Oracle database by a PostgreSQL database without modifying the source code ?

I thought about adding an adapter/wrapper module between my source code and the PostgreSQL db for the source code to be compatible with PostgreSQL.

Thanks

clouvis
  • 567
  • 1
  • 6
  • 18
  • 1
    The SQL syntax will have differences. As far as I know, no such adapters exist. If you change databases (*any* databases), you will almost certainly have to change SQL in your source code. – pmdba Feb 24 '22 at 15:38
  • Probably not ... did you write your queries using only ANSI SQL (and even then you would probably need to have limited your queries to the subset of the syntax supported by both databases)? If you did then the answer is maybe; if not then it is almost certain that you will find queries that only run on Oracle and the syntax is not supported on PostgreSQL (and the same would be true for **ANY** pair of RDBMSes as they all use different syntaxes and have differing levels of support/compliance for the ANSI standard and all also use their own proprietary non-standard syntax). – MT0 Feb 24 '22 at 15:40
  • PostgreSQL's SQL implementation is quite standard. Oracle's implementation is older (from the 70s) so it deviates quite a bit from the SQL Standard. You'll need to regularize the SQL you currently have in order to run in PostgreSQL. – The Impaler Feb 24 '22 at 17:23

1 Answers1

0

PostgreSQL is strict about ISO SQL standard implementation. Most recent Oracle versions are also compliant. Oracle usually implemented some feature long before it was standardized therefor their dialects is slightly different. Nowadays various SQL features have duplicate implementation in Oracle, an original one a ISO one.

  • Look Ora2pg, is this a Perl code which can convert a lot of Oracle code into Postgres.

  • Try to modify your Oracle code in Oracle database to be ISO SQL compliant, even before starting actual migration. Such a code can run on both databases without any changes.

  • Then you finally move from Oracle to Postgres

ibre5041
  • 4,903
  • 1
  • 20
  • 35