8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Override External Table Parameters From a Query in Oracle Database 12c Release 2 (12.2)
Oracle Database 12c Release 2 (12.2) allows a query to modify some external table parameters using the EXTERNAL MODIFY
clause of the SELECT
statement. The following parameters can be modified.
ACCESS PARAMETERS
: Only{NO}BADFILE
,{NO}LOGFILE
and{NO}DISCARDFILE
changes are supported. Can't use bind variables.DEFAULT DIRECTORY
: Can't use bind variables.LOCATION
: Can be a literal or bind variable.REJECT LIMIT
: Can be a literal or bind variable.
When using partitioned external tables only the table-level settings can be altered using this method. Partition and subpartiton settings will not be affected.
- Setup
- Non-Partitioned External Table
- Partitioned External Table
- PL/SQL Support
- Security Implications
Related articles.
- External Tables : All Articles
- External Tables : Querying Data From Flat Files in Oracle
- Partitioned External Tables in Oracle Database 12c Release 2 (12.2)
- Override External Table Parameters and Inline External Tables
Setup
In order to demonstrate an 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;
Create an external table to load the data. The attributes we can modify are shown in bold.
CONN test/test@pdb1 DROP TABLE tab_ext; CREATE TABLE tab_ext ( country_code VARCHAR2(3), object_id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128) ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY tmp_dir1 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE tmp_dir1 LOGFILE tmp_dir1:'part_tab_ext_%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;
Check the current contents of the external table without any modifiers.
COLUMN country_code FORMAT A12 SELECT country_code, COUNT(*) AS amount FROM tab_ext GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 2000 SQL>
Non-Partitioned External Table
Modify the ACCESS PARAMETERS
file parameters.
SELECT country_code, COUNT(*) AS amount FROM tab_ext EXTERNAL MODIFY ( ACCESS PARAMETERS ( NOBADFILE NOLOGFILE NODISCARDFILE ) ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 2000 SQL>
Modify the DEFAULT DIRECTORY
parameter.
SELECT country_code, COUNT(*) AS amount FROM tab_ext EXTERNAL MODIFY ( DEFAULT DIRECTORY tmp_dir2 ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 2000 SQL>
Modify the LOCATION
parameter.
SELECT country_code, COUNT(*) AS amount FROM tab_ext EXTERNAL MODIFY ( LOCATION ('gbr1.txt', 'gbr2.txt') ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 SQL>
Modify the REJECT LIMIT
parameter.
SELECT country_code, COUNT(*) AS amount FROM tab_ext EXTERNAL MODIFY ( REJECT LIMIT 5 ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 2000 SQL>
Modify all available parameters.
SELECT country_code, COUNT(*) AS amount FROM tab_ext EXTERNAL MODIFY ( DEFAULT DIRECTORY tmp_dir2 ACCESS PARAMETERS ( BADFILE tmp_dir2:'part_tab_ext_%a_%p.bad' LOGFILE tmp_dir2 DISCARDFILE tmp_dir2 ) LOCATION ('gbr1.txt', 'ire1.txt', 'ire2.txt') REJECT LIMIT 5 ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 1000 IRE 2000 SQL>
Partitioned External Table
A full explanation of partitioned external tables can be seen here.
When using partitioned external tables only the table-level settings can be altered using this method. Partition and subpartiton settings will not be affected.
DROP TABLE part_tab_ext; CREATE TABLE part_tab_ext ( country_code VARCHAR2(3), object_id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128) ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY tmp_dir1 ACCESS PARAMETERS ( FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL ( country_code, object_id, owner, object_name ) ) ) REJECT LIMIT UNLIMITED PARTITION BY LIST (country_code) ( PARTITION part_gbr VALUES ('GBR') LOCATION ('gbr1.txt', 'gbr2.txt'), PARTITION part_usa VALUES ('IRE') DEFAULT DIRECTORY tmp_dir2 LOCATION ('ire1.txt', 'ire2.txt'), PARTITION part_others VALUES ('XXX') );
Modify all available parameters.
SELECT country_code, COUNT(*) AS amount FROM tab_ext EXTERNAL MODIFY ( DEFAULT DIRECTORY tmp_dir2 ACCESS PARAMETERS ( BADFILE tmp_dir2:'part_tab_ext_%a_%p.bad' LOGFILE tmp_dir2 DISCARDFILE tmp_dir2 ) REJECT LIMIT 5 ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 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 tab_ext EXTERNAL MODIFY ( LOCATION ('gbr1.txt', 'gbr2.txt') ) GROUP BY country_code ORDER BY 1) LOOP DBMS_OUTPUT.put_line(cur_rec.country_code || ':' || cur_rec.amount); END LOOP; END; / GBR:2000 PL/SQL procedure successfully completed. SQL>
Here's an example of a SELECT ... INTO.
SET SERVEROUTPUT ON DECLARE l_country_code VARCHAR2(3); l_amount NUMBER; BEGIN SELECT country_code, COUNT(*) AS amount INTO l_country_code, l_amount FROM tab_ext EXTERNAL MODIFY ( LOCATION ('gbr1.txt', 'gbr2.txt') ) 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.
As pointed out by Pete Finnigan, you need to consider the security implications of this functionality. At the time you define the external table you have made a conscious decision about the directory objects and file locations you are going to use. The ability to alter these parameters at runtime mean you need to pay special attention to the directory objects that are available to the user, or risk a security issue.
Create a second test user.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; CREATE USER test2 IDENTIFIED BY test2; GRANT CREATE SESSION TO test2;
Create the basic external table from the first example in the initial test user, and grant access to the new test user.
CONN test/test@pdb1 DROP TABLE tab_ext; CREATE TABLE tab_ext ( country_code VARCHAR2(3), object_id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128) ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY tmp_dir1 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE tmp_dir1 LOGFILE tmp_dir1:'part_tab_ext_%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; GRANT SELECT ON tab_ext To test2;
Now attempt to use the external table from the new test user.
CONN test2/test2@pdb1 COLUMN country_code FORMAT A12 SELECT country_code, COUNT(*) AS amount FROM test.tab_ext GROUP BY country_code ORDER BY 1; * ERROR at line 2: ORA-06564: object TMP_DIR1 does not exist SQL>
As we can see, the external table is run in the context of the current user, which doesn't have access to the directory objects. Let's grant access and try again.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; GRANT READ, WRITE ON DIRECTORY tmp_dir1 TO test2; CONN test2/test2@pdb1 COLUMN country_code FORMAT A12 SELECT country_code, COUNT(*) AS amount FROM test.tab_ext GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 2000 IRE 2000 SQL>
This demonstrates the basic external table has now worked. We can see below we are able to alter the runtime parameters from the new test user.
SELECT country_code, COUNT(*) AS amount FROM test.tab_ext EXTERNAL MODIFY ( ACCESS PARAMETERS ( BADFILE tmp_dir1:'part_tab_ext_%a_%p.bad' LOGFILE tmp_dir1 NODISCARDFILE ) LOCATION ('gbr1.txt', 'ire1.txt', 'ire2.txt') REJECT LIMIT 5 ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 1000 IRE 2000 SQL>
If another directory object is available to the new test user, we can also alter the directory references.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; GRANT READ, WRITE ON DIRECTORY tmp_dir2 TO test2; CONN test2/test2@pdb1 COLUMN country_code FORMAT A12 SELECT country_code, COUNT(*) AS amount FROM test.tab_ext EXTERNAL MODIFY ( DEFAULT DIRECTORY tmp_dir2 ACCESS PARAMETERS ( BADFILE tmp_dir2:'part_tab_ext_%a_%p.bad' LOGFILE tmp_dir2 NODISCARDFILE ) LOCATION ('gbr1.txt', 'ire1.txt', 'ire2.txt') REJECT LIMIT 5 ) GROUP BY country_code ORDER BY 1; COUNTRY_CODE AMOUNT ------------ ---------- GBR 1000 IRE 2000 SQL>
So you must make sure you control what directory objects are visible to the user accessing the external table, or you could find yourself in a position where you are exposing data you shouldn't be.
For more information see:
- Overriding Parameters for External Tables in a Query
- External Tables : All Articles
- External Tables : Querying Data From Flat Files in Oracle
- Partitioned External Tables in Oracle Database 12c Release 2 (12.2)
- Override External Table Parameters and Inline External Tables
Hope this helps. Regards Tim...