5 ms·

I don't get what this is. Is this a postgres fork? An extension? The documentation says it's Version 2 but the docs are rather lacking. Having something providi

by pfix 4y ago

I don't get what this is. Is this a postgres fork? An extension? The documentation says it's Version 2 but the docs are rather lacking. Having something providing an "Oracle API" on Postgres would be awesome but not sure if this works like that?

paulryanrogers 4y ago
> IvorySQL has imported and enhanced the Orafce extension to add support for numerous Oracle compatibility features including built-in PACKAGES, data types, and conversion functions Sounds and looks like a fork to me. EnterpriseDB used to offer some Oracle compatibility built on Pg, as a fork IIRC. Don't see it on their site any more though. EDIT: Much thanks to replies with links to EDB.
- asah 4y ago
  EDB PostgreSQL is still very much alive and Oracle compatibility is a big success among enterprises that have chosen to migrate and need to smooth the process.
- skissane 4y ago
  Does it need to be a fork, as opposed to just an extension? I understand why upstream Postgres might not want to put Oracle compatibility in the core, since it would be adding a lot of crud which only a minority of people would benefit from. However, making sure the Postgres core has the extension points necessary to support extensions providing compatibility with Oracle (or DB2, MSSQL, MySQL, etc), is something they might be more willing to entertain.
  - chasil 4y ago
    The postgres 15 manual has a chapter on Oracle compatibility. I don't know where to find it on their website, but the docs RPM that they provide bundles the PDF.
    - skissane 4y ago
      You are probably thinking of ECPG's "Oracle compatibility mode", which makes it behave more like Oracle Pro*C - https://www.postgresql.org/docs/15/ecpg-oracle-compat.html https://www.postgresql.org/docs/15/ecpg-oracle-compat.html However, few people actually use Pro*C nowadays. I spent almost 10 years working for Oracle, and I never once came across anybody using it. I think almost all use of it is in legacy applications which date back to the 1980s, first half of the 1990s at the latest. Obviously still enough use for Oracle to keep on supporting it, even with some occasional minor enhancements, but rather fringe all the same. I don't think many people use ECPG either. I'm sure some people must, but I myself have never seen it. Probably most use is in porting existing applications from Oracle Pro*C, or its equivalents such as Informix E/SQL. Actually, it is interesting to observe that the section on ECPG's Informix compatibility mode is a lot longer than the Oracle equivalent. I don't know if that's because less compatibility is required (maybe Pro*C is closer to ECPG already), or if that's because Informix compatibility has received more investment.
      - thorin 4y ago
        I've used pro* C for a couple of things (in banking and utilties, 1990s) where people wanted interactive batch functionality for something more complex than you'd generally put in a shell script. I think most of the pro* C usage came from pre Oracle 7 when PL/SQL didn't exist.
- znpy 4y ago
  That sounds like the description of a distribution of postgresql to me. Think of a gnu/linux distro, that gives you some pre-packaged software built with an intended goal (more or less general) and according to a certain set of opinions.
- tamasnet 4y ago
  Their Oracle compatibility is still a thing, at least commercially: https://www.enterprisedb.com/products/edb-postgres-advanced-server https://www.enterprisedb.com/products/edb-postgres-advanced-...
- sbuttgereit 4y ago
  That was the whole hook for EnterpriseDB. They still sell the product: https://www.enterprisedb.com/products/edb-enterprise https://www.enterprisedb.com/products/edb-enterprise The difference here is that the EnterpriseDB product is not open source.
  - chasil 4y ago
    IBM Db/2 also bought it, and uses it to implement SQL/PSM. https://www.enterprisedb.com/resources/ibmr-puts-oracle-sword-enterprisedb https://www.enterprisedb.com/resources/ibmr-puts-oracle-swor...
- rst 4y ago
  Also, their docs leave me wondering about the level of Oracle compatibility they're claiming. Their page on the subject, https://www.ivorysql.org/docs/compatibillity_features/hierarchical/ https://www.ivorysql.org/docs/compatibillity_features/hierar... , lists a bunch of immediate sore points -- date types and functions, the CONNECT BY query syntax, etc. But there are other things Oracle supports that are not listed, and still potentially serious stumbling blocks -- "fast" (incremental) automatic refresh of materialized views, IGNORE NULLS in window functions, and so forth. Oddly, I'd take their compatibility claims more seriously if they were more clearly up-front about the limits.
  - knutwannheden 4y ago
    Am I one of the only developers who actually likes the CONNECT BY queries? Often I think they are a very succinct alternative to recursive CTEs.
  - chasil 4y ago
    I'll just fire this up, create some external tables, and use the v7 join syntax to write out my data with utl_file.
Alupis 4y ago
Can someone elaborate on what specifically something like this would be used for? I would imagine if you're going through the pain of migrating off Oracle, picking something like IvorySQL is just a half-measure. Why not just make the switch to PostgreSQL (or other) and be done with worrying about compatibility? How long will this project last, and how great will the compatibility actually be - is it a drop-in replacement? Most folks use ORM's these days too - making switching databases simple (although not always trivial). So why does this need to exist?
- tomrod 4y ago
  '' is NULL in Oracle. The switch is non-trivial.
- u320 4y ago
  Because migrating off Oracle to an Oracle-compatible DB is less work than migrating to straight Postgres. This is not rocket science.
- kijin 4y ago
  Full migration can take months if not years. Meanwhile, a drop-in replacement will allow you to stop paying Oracle immediately. I wouldn't be too optimistic about ORM usage in the kinds of organizations that chose Oracle in the first place.
- Schnitz 4y ago
  Oracle is expensive. Salesforce has been developing something similar internally for years to replace Oracle.
  - jamesfinlayson 4y ago
    So did Amazon/AWS I think.
- SoftTalker 4y ago
  If you're using an ORM then you're by default using the database fairly generically. I'd ask why you are paying for any commercial DB in that case. Organizations who committed to and paid for Oracle are probably using a lot of the features that are unique to Oracle. Or they have a lot of legacy that is. If IvorySQL can take all their custom plsql packages and ETL batch jobs and run them without changes that's a big win. It's not clear to me that it really can do that, though.

by pfix 4y ago

paulryanrogers 4y ago

> IvorySQL has imported and enhanced the Orafce extension to add support for numerous Oracle compatibility features including built-in PACKAGES, data types, and conversion functions Sounds and looks like a fork to me. EnterpriseDB used to offer some Oracle compatibility built on Pg, as a fork IIRC. Don't see it on their site any more though. EDIT: Much thanks to replies with links to EDB.

asah 4y ago
EDB PostgreSQL is still very much alive and Oracle compatibility is a big success among enterprises that have chosen to migrate and need to smooth the process.
skissane 4y ago
Does it need to be a fork, as opposed to just an extension? I understand why upstream Postgres might not want to put Oracle compatibility in the core, since it would be adding a lot of crud which only a minority of people would benefit from. However, making sure the Postgres core has the extension points necessary to support extensions providing compatibility with Oracle (or DB2, MSSQL, MySQL, etc), is something they might be more willing to entertain.
- chasil 4y ago
  The postgres 15 manual has a chapter on Oracle compatibility. I don't know where to find it on their website, but the docs RPM that they provide bundles the PDF.
  - skissane 4y ago
    You are probably thinking of ECPG's "Oracle compatibility mode", which makes it behave more like Oracle Pro*C - https://www.postgresql.org/docs/15/ecpg-oracle-compat.html https://www.postgresql.org/docs/15/ecpg-oracle-compat.html However, few people actually use Pro*C nowadays. I spent almost 10 years working for Oracle, and I never once came across anybody using it. I think almost all use of it is in legacy applications which date back to the 1980s, first half of the 1990s at the latest. Obviously still enough use for Oracle to keep on supporting it, even with some occasional minor enhancements, but rather fringe all the same. I don't think many people use ECPG either. I'm sure some people must, but I myself have never seen it. Probably most use is in porting existing applications from Oracle Pro*C, or its equivalents such as Informix E/SQL. Actually, it is interesting to observe that the section on ECPG's Informix compatibility mode is a lot longer than the Oracle equivalent. I don't know if that's because less compatibility is required (maybe Pro*C is closer to ECPG already), or if that's because Informix compatibility has received more investment.
    - thorin 4y ago
      I've used pro* C for a couple of things (in banking and utilties, 1990s) where people wanted interactive batch functionality for something more complex than you'd generally put in a shell script. I think most of the pro* C usage came from pre Oracle 7 when PL/SQL didn't exist.
znpy 4y ago
That sounds like the description of a distribution of postgresql to me. Think of a gnu/linux distro, that gives you some pre-packaged software built with an intended goal (more or less general) and according to a certain set of opinions.
tamasnet 4y ago
Their Oracle compatibility is still a thing, at least commercially: https://www.enterprisedb.com/products/edb-postgres-advanced-server https://www.enterprisedb.com/products/edb-postgres-advanced-...
sbuttgereit 4y ago
That was the whole hook for EnterpriseDB. They still sell the product: https://www.enterprisedb.com/products/edb-enterprise https://www.enterprisedb.com/products/edb-enterprise The difference here is that the EnterpriseDB product is not open source.
- chasil 4y ago
  IBM Db/2 also bought it, and uses it to implement SQL/PSM. https://www.enterprisedb.com/resources/ibmr-puts-oracle-sword-enterprisedb https://www.enterprisedb.com/resources/ibmr-puts-oracle-swor...
rst 4y ago
Also, their docs leave me wondering about the level of Oracle compatibility they're claiming. Their page on the subject, https://www.ivorysql.org/docs/compatibillity_features/hierarchical/ https://www.ivorysql.org/docs/compatibillity_features/hierar... , lists a bunch of immediate sore points -- date types and functions, the CONNECT BY query syntax, etc. But there are other things Oracle supports that are not listed, and still potentially serious stumbling blocks -- "fast" (incremental) automatic refresh of materialized views, IGNORE NULLS in window functions, and so forth. Oddly, I'd take their compatibility claims more seriously if they were more clearly up-front about the limits.
- knutwannheden 4y ago
  Am I one of the only developers who actually likes the CONNECT BY queries? Often I think they are a very succinct alternative to recursive CTEs.
- chasil 4y ago
  I'll just fire this up, create some external tables, and use the v7 join syntax to write out my data with utl_file.

Alupis 4y ago

Can someone elaborate on what specifically something like this would be used for? I would imagine if you're going through the pain of migrating off Oracle, picking something like IvorySQL is just a half-measure. Why not just make the switch to PostgreSQL (or other) and be done with worrying about compatibility? How long will this project last, and how great will the compatibility actually be - is it a drop-in replacement? Most folks use ORM's these days too - making switching databases simple (although not always trivial). So why does this need to exist?

tomrod 4y ago
'' is NULL in Oracle. The switch is non-trivial.
u320 4y ago
Because migrating off Oracle to an Oracle-compatible DB is less work than migrating to straight Postgres. This is not rocket science.
kijin 4y ago
Full migration can take months if not years. Meanwhile, a drop-in replacement will allow you to stop paying Oracle immediately. I wouldn't be too optimistic about ORM usage in the kinds of organizations that chose Oracle in the first place.
Schnitz 4y ago
Oracle is expensive. Salesforce has been developing something similar internally for years to replace Oracle.
- jamesfinlayson 4y ago
  So did Amazon/AWS I think.
SoftTalker 4y ago
If you're using an ORM then you're by default using the database fairly generically. I'd ask why you are paying for any commercial DB in that case. Organizations who committed to and paid for Oracle are probably using a lot of the features that are unique to Oracle. Or they have a lot of legacy that is. If IvorySQL can take all their custom plsql packages and ETL batch jobs and run them without changes that's a big win. It's not clear to me that it really can do that, though.