These are some of the subclauses you may wish to use:<\/p>\n
- \n
- DELIMITED BY: This subclause expresses how the fields are delimited and terminated.<\/li>\n
- LOAD WHEN: Using this subclause we can supply a conditional statement by which the decision to load or not load a record is made.<\/li>\n
- BADFILE: Specifies a file (including a valid directory) to which erroring records should be written. If a record does not pass the LOAD WHEN condition it is not written to the bad file.\u00a0 Instead there is a subclause, DISCARDFILE, that specifies where such records should be logged.<\/li>\n
- LOGFILE: A valid Oracle directory and file name for all logging.<\/li>\n
- SKIP: This specifies a number of records that should be skipped before loading begins. If, for example, your csv begins with a header record, you will want to skip the first record.<\/li>\n<\/ul>\n
The field_definitions<\/strong> clause details the fields in the data file and outlines how they build up the record. This clause must begin with the FIELDS keyword.<\/p>\n
- \n
- ENCLOSED BY: Shows the string(s) that encapsulates each field.<\/li>\n
- TERMINATED BY: Specifies the string that terminates the fields or the keyword WHITESPACE.<\/li>\n<\/ul>\n
The column transformations outline any transformations that should be applied to each column. Transformations are specified column-by-column.<\/p>\n
- \n
- NULL: If you apply the NULL transform, the column will be set to null irrespective of what is in the data file.<\/li>\n
- CONSTANT: With the CONSTANT transform, the column will be set to the specified string for all records.<\/li>\n<\/ul>\n
Example<\/h4>\n
An example will probably help you make some sense out of my huge spewing of subclauses.<\/p>\n
Let\u2019s assume the Sales team create a daily spreadsheet of sales details, and we need to build an external table to query it.<\/p>\n
![\"csv\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2014\/10\/csv.jpg\")
<\/p>\nCREATE TABLE ext_daily_sales (\r\n Sale_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DATE,\r\n Sale_item\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(100),\r\n Quantity\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NUMBER\r\n)\r\nORGANIZATION EXTERNAL\r\n(TYPE ORACLE_LOADER\r\n DEFAULT DIRECTORY sales_dir\r\n ACCESS PARAMETERS (\r\n RECORDS DELIMITED BY NEWLINE\r\n LOAD WHEN (quantity > 1)\r\n BADFILE sales_dir:'dailysales.bad'\r\n LOGFILE sales_dir:'dailysales.log'\r\n DISCARDFILE sales_dir:'dailysales-discarded.log'\r\n SKIP 1\r\n FIELDS delimited by ','\r\n (sale_time char(20) DATE_FORMAT DATE mask \"dd\/mm\/yyyy hh24:mi:ss\",\r\n Sale_item,\r\n Quantity)\r\n )\r\n LOCATION ('daily sales report.csv')\r\n);<\/pre>\nThere are a few things to note. The RECORDS and FIELDS keywords must be present \u2013 unless you completely exclude the record_format_info and\/or field_definition clauses. Secondly, your file names \u2013 as used, for example, in the badfile and logfile subclauses \u2013 should contain your Oracle directory name concatenated to your file name by means of a colon. If you omit the directory name, the file will be created in your DEFAULT DIRECTORY.<\/p>\n
XML<\/h4>\n
At first glance there seems to be very little leeway at the heart of the external table functionality; however, with just a little cunning, we can use it to read XML files.<\/p>\n
Assume the Daily Sales Total report that we receive is an XML file.\u00a0 We could create an external table upon it.<\/p>\n
![\"xml\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2014\/10\/xml.jpg\")
<\/p>\nCREATE TABLE ext_sales_totals (\r\n Sale_date\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DATE,\r\n Sale_item\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(100),\r\n Quantity\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NUMBER\r\n)\r\nORGANIZATION EXTERNAL\r\n(TYPE ORACLE_LOADER\r\n DEFAULT DIRECTORY sales_dir\r\n ACCESS PARAMETERS (\r\n RECORDS DELIMITED BY '<\/Item>'\r\n LOGFILE 'sales_totals.log'\r\n FIELDS (\r\n Sale_date CONSTANT SYSDATE,\r\n Sale_item\u00a0 CHAR(100) ENCLOSED BY '<Name>' AND '<\/Name>',\r\n Quantity CHAR(5) ENCLOSED BY '<Quantity>' AND '<\/Quantity>')\r\n )\r\n LOCATION ('sales totals.xml')\r\n);<\/pre>\nModifying External Tables<\/h4>\n
The sands can shift beneath the feet of an external table quite easily.\u00a0 At the close of each business day, the sales team create new versions of daily sales report.csv<\/i> and sales totals.xml<\/i> and overwrite the old copies of these files.\u00a0 That way, when we rerun our scripts the next morning, we have a whole new suite of data to upload.<\/p>\n
If, however, you need to make structural – DDL – amendments to your external table, this is also possible.\u00a0 If, for example, your default directory or your file name changes, you can issue the following alter statements.<\/p>\n
ALTER TABLE ext_daily_sales\r\nDEFAULT DIRECTORY new_sales_dir;<\/pre>\n
ALTER TABLE ext_sales_totals\r\nLOCATION ('new sales totals.xml');<\/pre>\n","protected":false},"excerpt":{"rendered":"At its most basic level, Oracle databases are built around data based within tables held within the database, and the moulding of that data into information and meaning. Under this model, data is created within the database or imported into it. There is, however, a third way. Using external tables, Oracle can query the contents of flat files held outsid…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48408,4217],"coauthors":[],"class_list":["post-73183","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-external-tables","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73183"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73183\/revisions"}],"predecessor-version":[{"id":91671,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73183\/revisions\/91671"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73183"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}