14) In Oracle INSERT INTO ... must be before WITH ... (but not after), while H2 supports WITH... INSERT INTO ... SELECT ...
On Wednesday, 7 October 2020 at 20:32:50 UTC+7 Andreas Reichel wrote: > 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 [email protected] > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1d3f431f-b5ae-420d-ab23-a0a3a989e2b5n%40googlegroups.com.
