The first really hidden gem in Oracle Database 12c Release 2: runtime modification of external table parameters

We missed to document some functionality !!!

With the next milestone for Oracle Database 12c Release 2 just taking place - the availability on premise for Linux x86-64, Solaris Sparc64, and Solaris x86-64, in addition to the Oracle Cloud - I managed to use this as an excuse to play around with it for a bit .. and found that we somehow missed to document new functionality. Bummer. But still better than the other way around .. ;-)

We missed to document the capability to override some parameters of an external table at runtime.

So I decided to quickly blog about this to not only fill the gap in documentation (doc bug is filed already) but also to ruthlessly hijack the momentum and to start highlighting new functionality (there's more blogs to come, specifically around my pet peeve Partitioning, but that's for later).

So what does it mean to override some parameters of an external table at runtime?

It simply means hat you can use one external table definition stub as proxy for external data access of different files, with different reject limits, at different points in time. Without the need to do a DDL to modify the external table definition.

The usage is pretty simple and straightforward, so let me quickly demonstrate this with a not-so-business-relevant sample table. The pre-requirement SQL for this one to run is at the end of this blog and might make its way onto github as well; I have not managed that yet and just wanted to get this blog post out.

Here is my rather trivial external table definition. Works for me since version 9, so why not using it with 12.2 as well.

CREATE TABLE et1 (col1 NUMBER, col2 NUMBER, col3 NUMBER)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY d1
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    NOBADFILE
    NOLOGFILE
    FIELDS TERMINATED BY ","
  )
  LOCATION ('file1.txt')
)
REJECT LIMIT UNLIMITED
;

SQL> SELECT count(*) FROM et1;

  COUNT(*)
----------
        99

SQL> SELECT count(*) FROM et1
     EXTERNAL MODIFY
     (LOCATION ('file2.txt'));

  COUNT(*)
----------
         9

SQL> SELECT table_name, location FROM user_external_locations WHERE table_name='ET1';

 TABLE_NAME                     LOCATION
 ------------------------------ ------------------------------
 ET1                            file1.txt

CREATE TABLE et2 (col1 NUMBER, col2 NUMBER, col3 NUMBER)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY d1
  ACCESS PARAMETERS
 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
   NOBADFILE
   NOLOGFILE
   FIELDS TERMINATED BY ","
 )
)
REJECT LIMIT UNLIMITED
;

SQL> SELECT * FROM et2;

no rows selected

SQL> SELECT count(*) FROM et1
     EXTERNAL MODIFY
     (LOCATION ('file2.txt'));

  COUNT(*)
----------
         9

You get the idea. Pretty cool stuff. 

Aaah, and to complete the short functional introduction: the following clauses can be over-ridden: DEFAULT DIRECTORY, LOCATION, ACCESS PARAMETERS (BADFILE, LOGFILE, DISCARDFILE) and REJECT LIMIT. 

That's about it for now for online modification capabilities for external tables. I am sure I have forgotten some little details here and there, but there's always soo many things to talk (write) about that you will never catch it all. And hopefully the documentation will cover it all rather sooner than later.

Stay tuned for now. There's more blog posts about 12.2 to come. And please, if you have any comments about this specific one or suggestions for future ones, then please let me know. You can always reach me at hermann.baer@oracle.com.

Cheers, over and out. 

And here's the most simple "data generation" I used for the examples above to get "something" in my files. Have fun playing.

rem my directory
rem create or replace directory d1 as '/tmp';

rem create some dummy data in /tmp
set line 300 pagesize 5000

spool /tmp/file1.txt
select rownum ||','|| 1 ||','|| 1 ||','  from dual connect by level < 100;
spool off

spool /tmp/file2.txt
select rownum ||','|| 22 ||','|| 22 ||','  from dual connect by level < 10;
spool off