Oracle 12c – How to Consume Data from Other Database in Stored Procedure

I can only attempt to answer for Oracle and postgres. After using Oracle exclusively for years, and postgres only for the last two years or so, I love postgres. There are so many small ways it is more convenient to use than Oracle, and it shares many of the crucial benefits (such as MVCC). It is easier to administer, reliable, has excellent documentation, and of course, it is free.

However postgres is no match for Oracle in some areas, such as:

• RAC - as far as I know there is no better clustering technology for any database
• RMAN - far superior to postgres's basic backup and recovery features, especially with block change tracking and incremental backups (which you can apply to other backups to keep an up to date full backup)
• Oracle support - exists. postgres support? Not so much
• many other features such as IOTs, 'Secure Files' and compression technology that have no analogue in the postgres world

Interestingly almost all af these features are absent or crippled in XE. I think I'd choose postgres over XE, all else being equal, but...

...none of this addresses the two biggest reasons for choosing Oracle:

1. You are already using Oracle and you have a huge investment in it (database independence is a silly myth)
2. Your developers and DBAs know Oracle and use its features to the full (why wouldn't they as database independence is a silly myth anyway?)

EDIT:

The one situation where I'd choose postgres over XE every time is if security is a concern. If your database or any of it's applications are exposed to the public internet I am not sure if XE is a good idea at all.

1. DETACH is not required, once you submit the job using dp api, it will run in the background. Once job starts, the dumpfile will remain unless job is killed. Once you run DBMS_DATAPUMP.START_JOB (handle => nhandle); the job will start executing. However, once you start and before DP begins (there is short delay when it prepares), if you detach during this time, the job is effectively cancelled and dumpfile will disappear.

2. Unless you need the dumpfile be backword compatible with certain prior versions of Oracle, you should leave this parameter alone, the default value is fine.