3 ms·

If you are desperate for greater functionality, you can install the free Oracle XE database, then write whatever functions you want in pl/SQL. Oracle "external

by chasil 4y ago

If you are desperate for greater functionality, you can install the free Oracle XE database, then write whatever functions you want in pl/SQL. Oracle "external tables" will allow you to see your CSVs directly, without creating a physical table within the database or otherwise using resources. If you actually have SQLite databases, you can join them in Oracle directly with dg4odbc inserted into your listener. Note that the SQL/PSM standard is based on Oracle pl/SQL, and many databases implement it; SQLite doesn't. "SQL/PSM is derived, seemingly directly, from Oracle's PL/SQL. Oracle developed PL/SQL and released it in 1991, basing the language on the US Department of Defense's Ada programming language." https://en.m.wikipedia.org/wiki/SQL/PSM https://en.m.wikipedia.org/wiki/SQL/PSM

Beltalowda 4y ago
Why Oracle XE and not PostgreSQL? What advantages does Oracle XE have over it?
- chasil 4y ago
  EnterpriseDB sells a version of Postgres with deep Oracle compatibility, but this emulation is not included in the free version. Many years ago, IBM bundled this code into db2, and it remains jointly developed as far as I know. The EnterpriseDB sales page for this product would be a good start on what's missing. https://www.enterprisedb.com/why-edb/leave-oracle-for-postgresql-migration-postgres-move https://www.enterprisedb.com/why-edb/leave-oracle-for-postgr...
  - marcosdumay 4y ago
    So you mean that the feature Oracle has that Postges is missing is Oracle compatibility (unless you install it from a 3rd party)? That page doesn't list features. It only shows you tools to help migrating.
    - chasil 4y ago
      There used to be PDF details there. Here is a synopsis, there is probably more. EDB Postgres Advanced Server is an enhanced version of PostgreSQL that offers compatibility with Oracle in the following areas: Oracle-specific and syntax-compatible database object types Oracle-specific data types Oracle-specific SQL extensions Oracle PL/SQL support as a built-in native procedural language Oracle data dictionary views (i.e., ALL_, DBA_, USER_ views) Oracle built in PL/SQL packages EDB Postgres Advanced Server doesn't fully implement all Oracle-specific features in these areas. Nevertheless, the compatibility is extensive and covers many of the most commonly used Oracle constructs. https://www.enterprisedb.com/docs/migrating/oracle/migration_techniques/ https://www.enterprisedb.com/docs/migrating/oracle/migration... (If you don't care for Oracle, don't use their database. However, SQL/PSM is the ISO PSM-96 standard; using this dialect will be maximally portable.)
  - Beltalowda 4y ago
    Okay, but that wasn't really my question. You recommend Oracle XE; I never used Oracle DB so I'm wondering what technical reasons there are to choose Oracle over PostgreSQL? The general attitude towards Oracle tends to be quite negative on HN. Maybe for good reason, but I'd like to hear a different perspective and this seemed like a good opportunity.
    - chasil 4y ago
      Oracle XE will have the deepest SQL/PSM implementation of any freely-available database, without question. The list of standard functions, procedures and packages is enormous. This is a double-edged sword. If you use these (proprietary) features and you outgrow XE, then you have to buy a license. XE also doesn't get quarterly patches - it's updated every couple of years (hardening is essential). Oracle Enterprise retails for $47,500/CPU core. The stripped-down SE2 falls to about $18k. Definitely beware of the lock-in, but XE likely remains the best for procedural programming in SQL. For the extremely Oracle-averse, there still may be some value in running it alongside Postgres(with the SQL/PSM extension) and MySQL to verify that your code is portable.
mhio 4y ago
At what point does the licensing kick in and the lawyers turn up on your door?
sigzero 4y ago
Windows and Linux only. So that's out.
- chasil 4y ago
  You can directly attach SQLite databases into an Oracle instance via dg4odbc. At this point, you have a much more expansive SQL dialect that you can use to query your data (I've never tried this in the XE edition, and when it works it can be somewhat brittle and quick to throw errors). Granted, most databases can do this. However, if you are specifically interested in SQL/PSM, you really can't do better than Oracle. Fyi, Microsoft/Sybase do not implement SQL/PSM at all; they use Transact-SQL. I've read that IBM db2 is the best implementation outside Oracle, but EnterpriseDB is likely identical. IBM db2 udb for Unix/Windows happens to have a community edition. https://www.ibm.com/db2/trials https://www.ibm.com/db2/trials

by chasil 4y ago

Beltalowda 4y ago

Why Oracle XE and not PostgreSQL? What advantages does Oracle XE have over it?

chasil 4y ago
EnterpriseDB sells a version of Postgres with deep Oracle compatibility, but this emulation is not included in the free version. Many years ago, IBM bundled this code into db2, and it remains jointly developed as far as I know. The EnterpriseDB sales page for this product would be a good start on what's missing. https://www.enterprisedb.com/why-edb/leave-oracle-for-postgresql-migration-postgres-move https://www.enterprisedb.com/why-edb/leave-oracle-for-postgr...
- marcosdumay 4y ago
  So you mean that the feature Oracle has that Postges is missing is Oracle compatibility (unless you install it from a 3rd party)? That page doesn't list features. It only shows you tools to help migrating.
  - chasil 4y ago
    There used to be PDF details there. Here is a synopsis, there is probably more. EDB Postgres Advanced Server is an enhanced version of PostgreSQL that offers compatibility with Oracle in the following areas: Oracle-specific and syntax-compatible database object types Oracle-specific data types Oracle-specific SQL extensions Oracle PL/SQL support as a built-in native procedural language Oracle data dictionary views (i.e., ALL_, DBA_, USER_ views) Oracle built in PL/SQL packages EDB Postgres Advanced Server doesn't fully implement all Oracle-specific features in these areas. Nevertheless, the compatibility is extensive and covers many of the most commonly used Oracle constructs. https://www.enterprisedb.com/docs/migrating/oracle/migration_techniques/ https://www.enterprisedb.com/docs/migrating/oracle/migration... (If you don't care for Oracle, don't use their database. However, SQL/PSM is the ISO PSM-96 standard; using this dialect will be maximally portable.)
- Beltalowda 4y ago
  Okay, but that wasn't really my question. You recommend Oracle XE; I never used Oracle DB so I'm wondering what technical reasons there are to choose Oracle over PostgreSQL? The general attitude towards Oracle tends to be quite negative on HN. Maybe for good reason, but I'd like to hear a different perspective and this seemed like a good opportunity.
  - chasil 4y ago
    Oracle XE will have the deepest SQL/PSM implementation of any freely-available database, without question. The list of standard functions, procedures and packages is enormous. This is a double-edged sword. If you use these (proprietary) features and you outgrow XE, then you have to buy a license. XE also doesn't get quarterly patches - it's updated every couple of years (hardening is essential). Oracle Enterprise retails for $47,500/CPU core. The stripped-down SE2 falls to about $18k. Definitely beware of the lock-in, but XE likely remains the best for procedural programming in SQL. For the extremely Oracle-averse, there still may be some value in running it alongside Postgres(with the SQL/PSM extension) and MySQL to verify that your code is portable.

mhio 4y ago

At what point does the licensing kick in and the lawyers turn up on your door?

sigzero 4y ago

Windows and Linux only. So that's out.

chasil 4y ago
You can directly attach SQLite databases into an Oracle instance via dg4odbc. At this point, you have a much more expansive SQL dialect that you can use to query your data (I've never tried this in the XE edition, and when it works it can be somewhat brittle and quick to throw errors). Granted, most databases can do this. However, if you are specifically interested in SQL/PSM, you really can't do better than Oracle. Fyi, Microsoft/Sybase do not implement SQL/PSM at all; they use Transact-SQL. I've read that IBM db2 is the best implementation outside Oracle, but EnterpriseDB is likely identical. IBM db2 udb for Unix/Windows happens to have a community edition. https://www.ibm.com/db2/trials https://www.ibm.com/db2/trials