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.
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).
Related articles.
- External Tables : All Articles
- Override External Table Parameters From a Query in Oracle Database 12c Release 2 (12.2)
- Override External Table Parameters and Inline External Tables
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:
- Using Inline External Tables
- External Tables : All Articles
- Override External Table Parameters From a Query in Oracle Database 12c Release 2 (12.2)
- Override External Table Parameters and Inline External Tables
Hope this helps. Regards Tim...