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 .. icon wink The first really hidden gem in Oracle Database 12c Release 2: runtime modification of external table parameters

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
;

Pretty straightforward vanilla external table. Let’s now see how many rows this external table returns (the simple “data generation” is at the end of this blog):

SELECT count(*) FROM et1;

  COUNT(*)
———-
        99
So far, so good. And now the new functionality. We will now access the exact
same external table but tell the database to do a runtime modification of the file (location) we are accessing:

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

  COUNT(*)
———-
         9
As you can see, the row count changes without me having done any change
to the external table definition like an ALTER TABLE. You will also see that nothing has
changed in the external table definition:

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

TABLE_NAME                     LOCATION
—————————— ——————————
ET1                            file1.txt
And there’s one more thing. You might have asked yourself right now, right this moment … why do I have to specify a location then for the initial external table creation? The
answer is simple: you do not have to do this anymore.

Here is my external table without a location:

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
;

When I now select from it, guess what: you won’t get any rows back. The location is NULL.

SQL> SELECT * FROM et2;

no rows selected
Personally I am not sure I would go for this approach rather than having at least one
dummy file in the location with a control record with some pre-defined
values to being able to tell whether there’s really no records or
whether there’s a programmatic mistake when you plan to always override the location. But as many things in life, that’s a choice. As you saw, you don’t have to.

Using this stub table in the same way as before gives me access to my data.

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
rem
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

Let’s block ads! (Why?)

The Data Warehouse Insider