8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Inline External Tables in Oracle Database 18c

Oracle Database 18c allows you to access data in flat files using an inline external table defined in a SELECT statement.

Inline External Tables

This is a step further than the ability to override external table parameters from a query introduced in Oracle Database 12c Release 2 (12.2).

Setup
Inline External Table
PL/SQL Support
Security Implications
Miscellaneous

Setup

In order to demonstrate an inline external table we need some data in flat files. The following code spools out four CSV files with 1000 rows each.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

SET MARKUP CSV ON QUOTE ON
SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK OFF PAGESIZE 0

SPOOL /tmp/gbr1.txt
SELECT 'GBR',
       object_id,
       owner,
       object_name
FROM   dba_objects
WHERE  object_id <= 2000
AND    rownum <= 1000;
SPOOL OFF

SPOOL /tmp/gbr2.txt
SELECT 'GBR',
       object_id,
       owner,
       object_name
FROM   dba_objects
WHERE  object_id BETWEEN 2000 AND 3999
AND    rownum <= 1000;
SPOOL OFF

SPOOL /tmp/ire1.txt
SELECT 'IRE',
       object_id,
       owner,
       object_name
FROM   dba_objects
WHERE  object_id <= 2000
AND    rownum <= 1000;
SPOOL OFF

SPOOL /tmp/ire2.txt
SELECT 'IRE',
       object_id,
       owner,
       object_name
FROM   dba_objects
WHERE  object_id BETWEEN 2000 AND 3999
AND    rownum <= 1000;
SPOOL OFF

SET MARKUP CSV OFF
SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK OFF PAGESIZE 14

Create two directory objects to access these files. In this case both are pointing to the same directory, but it will still allow us to see the syntax.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE OR REPLACE DIRECTORY tmp_dir1 AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY tmp_dir1 TO test;

CREATE OR REPLACE DIRECTORY tmp_dir2 AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY tmp_dir2 TO test;

CONN test/test@pdb1

Inline External Table

An inline external table allows the external table definition to be placed in the FROM clause of a SQL statement using the EXTERNAL clause, so there is no need for an external table to be explicitly created.

SELECT country_code, COUNT(*) AS amount
FROM   EXTERNAL (
         (
           country_code  VARCHAR2(3),
           object_id     NUMBER,
           owner         VARCHAR2(128),
           object_name   VARCHAR2(128)
         )
         TYPE oracle_loader
         DEFAULT DIRECTORY tmp_dir1
         ACCESS PARAMETERS (
           RECORDS DELIMITED BY NEWLINE
           BADFILE tmp_dir1
           LOGFILE tmp_dir1:'inline_ext_tab_%a_%p.log'
           DISCARDFILE tmp_dir1
           FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
           MISSING FIELD VALUES ARE NULL (
             country_code,
             object_id,
             owner,
             object_name 
           )
        )
        LOCATION ('gbr1.txt', 'gbr2.txt', 'ire1.txt', 'ire2.txt')
        REJECT LIMIT UNLIMITED
      ) inline_ext_tab
GROUP BY country_code
ORDER BY 1;

COUNTRY_CODE     AMOUNT
------------ ----------
GBR                2000
IRE                2000

SQL>

In the following example we use a different directory object, and specify a different list of files in the LOCATION clause. This, not surprisingly gives us a different result.

SELECT country_code, COUNT(*) AS amount
FROM   EXTERNAL (
         (
           country_code  VARCHAR2(3),
           object_id     NUMBER,
           owner         VARCHAR2(128),
           object_name   VARCHAR2(128)
         )
         TYPE oracle_loader
         DEFAULT DIRECTORY tmp_dir2
         ACCESS PARAMETERS (
           RECORDS DELIMITED BY NEWLINE
           BADFILE tmp_dir2
           LOGFILE tmp_dir2:'inline_ext_tab_%a_%p.log'
           DISCARDFILE tmp_dir2
           FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
           MISSING FIELD VALUES ARE NULL (
             country_code,
             object_id,
             owner,
             object_name 
           )
        )
        LOCATION ('gbr1.txt', 'gbr2.txt')
        REJECT LIMIT UNLIMITED
      ) inline_ext_tab
GROUP BY country_code
ORDER BY 1;

COUNTRY_CODE     AMOUNT
------------ ----------
GBR                2000

SQL>

The inline external table definition is a little ugly, so you may prefer to put it into a WITH clause if you are planning to join it to other tables.

WITH inline_ext_tab AS (
  SELECT *
  FROM   EXTERNAL (
           (
             country_code  VARCHAR2(3),
             object_id     NUMBER,
             owner         VARCHAR2(128),
             object_name   VARCHAR2(128)
           )
           TYPE oracle_loader
           DEFAULT DIRECTORY tmp_dir2
           ACCESS PARAMETERS (
             RECORDS DELIMITED BY NEWLINE
             BADFILE tmp_dir2
             LOGFILE tmp_dir2:'inline_ext_tab_%a_%p.log'
             DISCARDFILE tmp_dir2
             FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
             MISSING FIELD VALUES ARE NULL (
               country_code,
               object_id,
               owner,
               object_name 
             )
          )
          LOCATION ('gbr1.txt', 'gbr2.txt')
          REJECT LIMIT UNLIMITED
        )
)
SELECT country_code, COUNT(*) AS amount
FROM   inline_ext_tab 
GROUP BY country_code
ORDER BY 1;

COUNTRY_CODE     AMOUNT
------------ ----------
GBR                2000

SQL>

PL/SQL Support

This syntax can also be used in the PL/SQL. Here's an example of a cursor for-loop.

SET SERVEROUTPUT ON
BEGIN
  FOR cur_rec IN (SELECT country_code, COUNT(*) AS amount
                  FROM   EXTERNAL (
                           (
                             country_code  VARCHAR2(3),
                             object_id     NUMBER,
                             owner         VARCHAR2(128),
                             object_name   VARCHAR2(128)
                           )
                           TYPE oracle_loader
                           DEFAULT DIRECTORY tmp_dir1
                           ACCESS PARAMETERS (
                             RECORDS DELIMITED BY NEWLINE
                             BADFILE tmp_dir1
                             LOGFILE tmp_dir1:'inline_ext_tab_%a_%p.log'
                             DISCARDFILE tmp_dir1
                             FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                             MISSING FIELD VALUES ARE NULL (
                               country_code,
                               object_id,
                               owner,
                               object_name 
                             )
                          )
                          LOCATION ('gbr1.txt', 'gbr2.txt', 'ire1.txt', 'ire2.txt')
                          REJECT LIMIT UNLIMITED
                        ) inline_ext_tab
                  GROUP BY country_code
                  ORDER BY 1)
  LOOP
    DBMS_OUTPUT.put_line(cur_rec.country_code || ':' || cur_rec.amount);
  END LOOP;
END;
/
GBR:2000
IRE:2000

PL/SQL procedure successfully completed.

SQL>

Here's an example of the SELECT ... INTO.

DECLARE
  l_country_code VARCHAR2(3);
  l_amount       NUMBER;
BEGIN
  SELECT country_code, COUNT(*) AS amount
  INTO   l_country_code, l_amount
  FROM   EXTERNAL (
           (
             country_code  VARCHAR2(3),
             object_id     NUMBER,
             owner         VARCHAR2(128),
             object_name   VARCHAR2(128)
           )
           TYPE oracle_loader
           DEFAULT DIRECTORY tmp_dir1
           ACCESS PARAMETERS (
             RECORDS DELIMITED BY NEWLINE
             BADFILE tmp_dir1
             LOGFILE tmp_dir1:'inline_ext_tab_%a_%p.log'
             DISCARDFILE tmp_dir1
             FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
             MISSING FIELD VALUES ARE NULL (
               country_code,
               object_id,
               owner,
               object_name 
             )
          )
          LOCATION ('gbr1.txt', 'gbr2.txt')
          REJECT LIMIT UNLIMITED
        ) inline_ext_tab
  GROUP BY country_code;
  
  DBMS_OUTPUT.put_line(l_country_code || ':' || l_amount);
END;
/
GBR:2000

PL/SQL procedure successfully completed.

SQL>

Security Implications

Access to directory objects should be controlled carefully, regardless of if you use external tables or not.

The security implications of dynamic access to directory objects and external files is discussed here.

Miscellaneous

Some thoughts and comments related to this functionality.

As stated in the documentation, this functionality doesn't support partitioned external tables, but that is irrelevant as you have full control of the files being accessed, so there is no need to consider partitioning.
As mentioned above, there are security considerations related to this functionality.
It results in really ugly SQL.
It could be useful in a situation where you don't have privilege to create a metadata object, for example a read-only database.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.