Ora2Pg – Oracle to PostgreSQL database schema converter
ora2pg.com
ora2pg.com
Orafce - An extension to bundle in Oracle functions -https://github.com/orafce/orafce
OracleFDW - A foreign data wrapper to query directly into Oracle from Postgres - https://github.com/laurenz/oracle_fdw
intro blog post: https://www.cybertec-postgresql.com/en/ora_migrator-moving-f...
Disclaimer: I work at CYBERTEC
In my case, the applications were relatively simple, but many of the queries used sequences explicitly, and the lack of a common syntax there was a slight pain point.
A viral license is "derivative works allowed only if they are distributed under the same license".
A permissive open source license is "derivative works allowed and they can be licensed freely".
I imagine any license oracle has offered on it's db is the no derivative works kind not an open source kind.
It's due in front of the supreme court October 7th (Wednesday after next).
It's... certainly a novel interpretation of what is copyrightable, and if they win it's going to cause quite a few problems (e.g. wine is going to be infringing windows copyright, and windows is going to be infringing Linux's copyright (with WSL and some of their other apis), and so on and so forth. Literally everyone, including oracle (see: sql, cloud apis, etc), has been acting like APIs aren't copyrightable.
So anyways, no, no case law (yet) since no one has even established that APIs are copyrightable.
This is incorrect. It was not. All* the source was rewritten.
* Minus a dozen lines or so, but that was found to be deminimis copying and not copyright infringement.
> They’re not implementing an API compatibility layer, they wrote a one time migration tool.
Writing a one time migration tool however does involve implementing a version of the API... I'm not sold on this distinguishing feature being of any importance, and I think the onus is on you to make a legal argument for why it is of importance (since proving the non-existence of a valid argument here is basically impossible)
Suppose, for example, Google had implemented there vm by creating a one time translation tool that translated java std lib apis to an internal google std lib apis in the bytecode, would that have changed the suit?
> Writing a one time migration tool however does involve implementing a version of the API
No, it doesn’t. It involves calling the API, and translating the data structures it returns to another API. If they provided a function identical to one in the Oracle API that did different things in Postgres, that might be different.
>I think the onus is on you to make a legal argument for why it is of importance
I’m not a lawyer so any legal argument I make is unlikely to hold water. Instead I will merely suggest that if this project reused 12,000 lines of SQL included in Oracle Database that would probably constitute infringement, but I don’t believe it does include that.
> Suppose, for example, Google had implemented there vm by creating a one time translation tool that translated java std lib apis to an internal google std lib apis in the bytecode, would that have changed the suit?
I’m not a lawyer, I have no idea and I find counterfactuals mostly uninteresting, especially when the exact details of Oracle vs Google are so frequently glossed over.
Which Oracle did not own and which Google used with a valid (up to the Apache Harmony code not being infringing by virtue of copying the API) license, I've literally never seen any suggestion that if Google had written this code themselves anything would have changed.
> Also, the de minimis was overturned on appeal.
Ah, you're right. Either way it's a separate question from the api portion of the case.
That’s not what I’m suggesting. If Google had written it themselves it would have had to have been identical, and that is why it’s infringement. The code is the same in the Android SDK as it is in Harmony and Java SE. It doesn’t matter where they got it from, it’s copyrighted, and the copyright belongs to Oracle.
I’m no fan of Oracle, but I also believe that Google has a history of playing fast and loose with copying other people’s technologies and asking for forgiveness later. My overall opinion about the consequences of that case are similar to those expressed in this article: https://www.wired.com/insights/2014/05/non-apocalypse-apis-c...
> - https://en.wikipedia.org/wiki/Google_v._Oracle_America
In what world does Oracle own the copyright to this code?
It might (if you buy Oracle's api argument) be an illegal derivative work of Oracle's api, but even that doesn't make them own it.
I'm also very curious to see a source that this is the same code as in Java SE, that seems highly unlikely...
Or is it that Oracle runs on the fumes of legacy lock ins?
I'm fed up with support (it takes so much work to open a ticket) and with the documentation (excessively verbose and full of marketing).
Mainframe is the CA of CAP.
Personally I chose SQLite sharded on S3 and dynamodb.
Why would they use AWS? They have their own cloud: https://cloud.ibm.com/catalog#services
This sounds interesting, would you mind sharing details?
But you can do a lot with Oracle XE and Apex (which are free). and you can link them up with Postgresql through HA or FDW.
The support is pretty good although it's usually only needed because the products themselves are filled with obscure bugs, edge cases and combinations of features which don't work well together.
The answer is still: No, not really.
The price tag of Oracles database license is simply to high, compared to any benefit you might get. It's much better to try to redesign your application to work around any feature you might be missing in Postgresql, or MariaDB.
For many organisations, the licensing costs of an Oracle or Microsoft RDBMS are trivial compared to the costs of the projects they're supporting, and it's easy to find where the value might be. Everything that's difficult about running an RDBMS at scale (clustering, failover/replication, statistics collection, plan management, monitoring, online index rebuilding, table partitioning...) is easier with the features and management tools those companies provide. Add actually decent support to that, and you've got a very clear value proposition.
A Postgres instance will require a lot more hands on management, and a deeper understanding of how the database engine works. At smaller scale, these factors are much less likely to cause problems, and many organisations are unlikely to ever reach actually large scales (many TB instance, running hundreds or thousands of transactions per second). But if you want to start scaling an RDBMS application up, you're going to have to confront those costs in one way or another. After a point, licensing expensive features can start to look better than deploying more specialised labor.
I remember some colleagues rolling out a project to thousands of postal service branch offices. Oracle was very happy to provide 24/7 emergency DBA services in case anything went wrong with any of the instances (local to the branch offices).
Just an additional guarantee of "if one of them behaves weirdly, we will be right on it" (as a service).
Unless you get a Postgres-based consultancy to do the same for you (and ofc they're not first party), that support is just unbeatable (in terms of what you're selling to your customer, the post offices).
But it also seems to be uncontroversial that no good could plausibly come out of attempting to sue Oracle.
You have to live with them anyway?
You obviously don't try to sue them, their company is 90% legal staff [0]. (Sorry I can't find the original source)
[0] https://am21.mediaite.com/tms/cnt/uploads/2011/06/org-charts...
Also some customers feel more safe when they know they've paid $10 000 or probably more for an oracle license.
Source: I once suggested to my boss that maybe we could save around $10 000 pr customer site and also save ourselves tons of work by using Postgres instead.
A TIMESTAMP WITH TIME ZONE data type that makes sense and is usable.
Firebird db is the only other database I know of that has package functionality. For a portable database, that is pretty impressive...
I am monthly approached by Oracle the opposite way tho, to migrate services I architect from Postgres to Oracle db. Never succumbed to this date (I would rather switch to farming TBH).
With this tool, I can go offensive :) Somebody also mentioned in the comments orafce extension - even more awesome, I can tell them that I already use Oracle db , its just inside Postgres which is technical detail :D.
Beware of John Deere though, I heard they're the Oracle of farming ;)
1. Ora2Pg will create a file that has all of your tables, columns, indices, keys etc. in Postgres format. Unfortunately it is great as a first pass, but we found that we had to do a lot of manually tweaking to get it into a really usable state. For instance, it does it's best guess at picking the right data type, but we didn't agree with all of its choices so we had to look through each one and decide which needed changing. Also, the way primary keys are setup on Oracle and Postgres is very different Ora2Pg takes a literal translation of translating how Oracle primary keys are set up instead of using the Postgres way - so we had to fix all of these.
2. It can't really convert functions and triggers. All of these had to be rewritten by hand.
3. It takes a ton of tweaking and experimentation to get Ora2Pg to transfer the data in a decent amount of time. Our first try took nearly 24 hears. We eventually tweaked it down to about an hour.
4. You will still need to rewrite all of your SQL queries into Postgres format. This is the part that took the most time since we have about 5,000 queries and each one had to be checked.
5. The biggest pain is that we couldn't stop development while the database migration was going on. So every time changes were made to the Oracle database, we had to manually make those same changes to the Postgres DDL files.
Sigh. I am so glad that project is behind me. I don't ever want to be involved in something like that again. It was totally worth it (Postgres is a dream to work with compared to Oracle), but the process was a nightmare.
Some notes about converting queries from Oracle to Postgres:
"foo IN (a, b, c)" can often be better done as "foo = ANY(ARRAY[a, b, c])" in Postgres - not least because you can shift to passing in an array of variable size without having to construct a special statement for each.
for sorting on the result of a function, consider using a subselect. "SELECT foo FROM (select fun(z) as foo from blaz) tmp order by 1".
replace nvl with coalesce
replace rownum <= 1 with LIMIT 1
replace listagg with string_agg
replace recursive hierarchy (start with/connect by/prior) with recursive
replace minus with except
replace SYSDATE with CURRENT_TIMESTAMP
replace trunc(sysdate) with CURRENT_DATE
replace trunc(datelastupdated) with DATE(datelastupduted) or datelastupdated::date
replace artificial date sentinels/fenceposts like to_date(’01 Jan 1900’) with '-infinity'::date
remove dual table references
replace decode with case statements
replace unique with distinct
replace to_number with ::integer
replace regexp_like() with ~ or ~*
replace mod with % operator
replace merge into with INSERT ... ON CONFLICT… DO UPDATE/NOTHING
change the default of any table using sys_guid() as a default to gen_random_uuid()
oracle pivot and unpivot do not work in postgres - use unnest
ORDER BY NLSSORT(english, 'NLS_SORT=generic_m') becomes ORDER BY gin(insensitive_query(english) gin_trgm_ops)
replace UNISTR( with U&’
Oracle: uses IS NULL to check for empty string; postgres uses empty string and null are different
If a varchar/text column has a unique index a check needs to be made to make sure empty strings are changed to nulls before adding or updating the column.
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias must be provided for it. - SELECT * FROM ( ) A
any functions in the order by clause must be moved to the select statement (e.g. order by lower(column_name))
Any sort of numeric/integer/bigint/etc. inside of a IN statement must not be a string (including 'null' - don't bother trying to use null="", it won't work).
Concatenating a NULL with a NOT NULL will result in a NULL.
Pay attention to any left joins. If a column from a left join is used in a where or select clause it might be null.
For sequences, instead of sequence_name.nextval use nextval('sequence_name')
I don't know anything about Oracle, and I realize the semantics of "null" are subtle, but this seems like a peculiar design decision that conflates two different things. With Oracle, how would one represent the state of a text field we don't know anything about? An empty string implies "we know the value and it is <nothing>" versus "it could be anything; we just don't know".
[edit: reworded for clarity]
By far and away, the way NULLs are handled in Oracle is the biggest problem with moving to a different database.
When using Oracle and the values are not static/a literal you should be using
foo = ANY(SELECT column_value FROM TABLE(?))
[1]: https://www.getdbt.com/product/
I’ve been looking at writing a script that parses the daily files for the California State Senate and State Assembly to create an API along with exposing a web app that offers a UI to view and search changes so folks interested can look around.
https://www.senate.ca.gov/dailyfile
https://clerk.assembly.ca.gov/daily-file
They give raw sql files in there but also a bunch of oracle that I have had no way to read.
This tool might help me see if there are differences in the file types.