This is maybe a bit unusual post, I almost never post about missing feature and rather focus what does exist. Although I love Oracle External tables, I must admit there are few missing features which can be easily implemented and would save a lot of time in some instances.
Please note most of the missing features below can be partially or fully patched by workarounds, I’ve done it million times before. The point I am trying to make is to have these implemented directly without any workarounds.
1. LOAD FIRST x
This is very simple, I would love to be able to specify to load first x for example first 2 records. This would be great in cases where you want to load for example header only.
Is there a workaround? Sure there is, but it’s a workaround and not a true solution
a. if you are lucky and the header has specific characteristic you can use filter LOAD WHEN
b. you can use REJECT LIMIT UNLIMITED – this is possible but I really do not like it, simply because you by definition expect bad records. What if the header is 5 rows and row 3 has an issue and is rejected?
c. SELECT * FROM EXTERNAL_TABLE WHERE ROWNUM < X
This is certainly possible, but SQL engine runs after the external table load (this is defined by readsize cache) and although this is somewhat working, you might still get rejects because Oracle might read 7 records instead of just 1.
d. use unix and separate header and body – this is clean solution but is extra step
2. LOAD LAST X
This is very similar to LOAD FIRST, this has however bigger implication. Just imagine you have 40GB file, if you use for example LOAD WHEN you will be waiting for a long time to get the trailing record. Unix file split is probably the most effective workaround here.
3. SKIP LAST X
External table supports SKIP X meaning skip first x records. This is great for skipping header during the load. I would love to see similar feature for skipping last X records (skipping loading trailing record). Again, there are possible workarounds, but why do we need them if it could to implement this directly to external tables?
Please share your ideas what could be added to external tables load.