Hello Dipesh, It all depends on your H2 version and particular syntax you have used. We run our Database Applications on H2, Oracle and SQL Server and so develop the schemas and queries with maximum compatibility in mind. Especially when you use Oracle-Mode, H2 should not be very different from Oracle, but there are a view exceptions of course:
1) Oracle can't truncate tables in other schemas, while H2 can 2) Oracle uses .NEXTVAL or .CURRVAL on sequences, while H2 (Version 2.0.201) enforces NEXT VALUE FOR when not in Oracle mode (but previous versions of H2 did not) 3) NUMBER vs. DECIMAL 4) before H2 Version 2.0.201, there was no VARBINARY and so you will need to decide about the column type case by case 5) before H2 Version 1.4.200 Git+ there were issues with Scale and Precision of auto-generated columns (which may need corrections) 6) EXTRACT fields from Dates/Times is very different between H2 and Oracle 7) Oracle will refuse FOREIGN KEY constraints, when there are no explicit PRIMARY KEYS on the columns (but H2 prior 2.0.201 created only implicit Unique Indexes) 8) in Oracle and empty String IS NULL, but in H2 and empty string IS NOT NULL 9) TRUNC on timestamps 10) Oracle is very sensitive to Table statistics and Query Plans. It can suddenly drop a Query Plan and chose something else. Also you might want to use query hints, especially PARALLEL, APPEND, DRIVING_SITE) to make your queries fast. 11) Oracle does not use CATALOGS 12) Lots of ALTER TABLE syntax in Oracle is weird (especially the COLUMN modifications) 13) You can not INSERT ... SELECT * FROM CLOBS/BLOBS in Oracle There is possibly a lot more stuff, please feel free to add. My advise is to export the schema ddl and then to run iit on Oracle step by step to see what fails and then to derive the pattern of the incompatibility. Once you have defined such a pattern, you can use JEDIT for REGEXP bases search and bean-shell based replace. Example for concerting .NEXTVAL into NEXT VALUE FOR fragments: Search Regex: (\w*)\.(\w*).NEXTVAL Beanshell Replacement: "NEXT VALUE FOR " + _1.toLowerCase() + "." + _2.toLowerCase() Good luck! Andreas On Tuesday, September 29, 2020 at 6:43:00 PM UTC+7 dipesh...@gmail.com wrote: > Hello Friends, > > We wanted to migrate H2 database to Oracle. > Does anyone performed this in past? any documentation? > > Your inputs will be much appreciated. > > > Thanks > Dipesh Tamore > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2647de98-8ff4-4960-84a4-b33aae737ad2n%40googlegroups.com.