{"id":7631,"date":"2020-02-28T09:54:59","date_gmt":"2020-02-28T09:54:59","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/use-oracle-external-tables\/"},"modified":"2026-11-03T19:28:47","modified_gmt":"2026-11-03T19:28:47","slug":"use-oracle-external-tables","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/use-oracle-external-tables\/","title":{"rendered":"Use Oracle external tables"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

External tables receive data from the flat files stored outside of the database and allow Oracle\u00ae to query the data in those files. Oracle can parse any file format supported by the SQL\\*Loader. You can’t use data manipulation language (DML) on external tables, but you can use DML for query, join, and sort operations. You can also create views and synonyms against the external tables.<\/p>\n

To take advantage of the external table feature, you should use the ORACLE_LOADER access driver and make sure the data files are in the text format. You should also understand Structured Query Language (SQL) so you can create an external table and perform queries against it. <\/p>\n

Make an external table in an Oracle database<\/strong><\/p>\n

Create a directory<\/strong><\/p>\n

Oracle can parse any file format supported by the “SQL\\*Loader.” You can’t use data manipulation language (DML) on external tables, but you can use DML for query, join, and sort operations. You can also create views and synonyms against the external tables.<\/p>\n

To take advantage of the external table feature, you should use the ORACLE_LOADER access driver and make sure the data files are in the text format. You should  also understand Structured Query Language (SQL) so you can create an external table and perform queries against it.<\/p>\n

Make an external table in an Oracle database<\/strong><\/p>\n

Create a directory<\/strong><\/p>\n

Use the following code to create a directory that points to the location of the comma-separated value (CSV) or text files provided by a user.<\/p>\n

 create directory vk_dir as ‘\/opt\/oracle\/app\/admin\/je1prd\/mods\/151214_CHG0004529’;<\/em><\/p>\n

Create an external table<\/strong><\/p>\n

Use the following SQL to create an external table:<\/em><\/p>\n

    create table kumarv5.VK_4529_ext
\n    (
\n      IBMCU   NCHAR(12),
\n      IBLITM   NCHAR(25),
\n      IBANPL   NUMBER
\n    )
\n    organization external
\n    (
\n       type oracle_loader
\n       default directory vk_dir
\n       access parameters
\n       (
\n         records delimited by newline
\n         logfile vk_dir:’VK_4529_ext.log’
\n         badfile vk_dir:’VK_4529_ext.bad’
\n         discardfile vk_dir:’VK_4529_ext.dcf’
\n         skip 1
\n         fields
\n         terminated by ‘~’
\n         missing field values are null
\n         reject rows with all null fields
\n         (
\n           IBMCU,
\n           IBLITM,
\n           IBANPL
\n         )
\n       )
\n       location
\n       (
\n         ‘data14.csv’
\n       )
\n    )
\n    reject limit unlimited;<\/em><\/p>\n

#### Create a local table<\/em><\/p>\n

Because you can’t perform DML on the external tables, use the following code to
\ncreate a local table based on the external one:<\/em><\/p>\n

    –create
\n    create table kumarv5.VK_4529_int
\n    (
\n      IBMCU   NCHAR(12),
\n      IBLITM   NCHAR(25),
\n      IBANPL   NUMBER
\n    )
\n    tablespace proddtat;<\/em>
\n <\/p>\n

Copy data from the external to the local table<\/strong><\/p>\n

Use the following code to copy data from the external to the local table:<\/p>\n

    –insert
\n    Insert into kumarv5.VK_4529_int   select * from kumarv5.VK_4529_ext;
\n    commit;
\n    select count(*) from kumarv5.VK_4529_int;<\/em><\/p>\n

Update the main production table<\/strong><\/p>\n

After you create the local table based on the external one, you can use the following code to update the main production table from the local table:<\/p>\n

    update PRODDTA.F43090 a
\n        set a.PCMCU=’         1AM’
\n        WHERE (a. IBMCU, a. IBLITM, a. IBANPL) in
\n           (select b. IBMCU, b. IBLITM, b. IBANPL from kumarv5.VK_4529_int b );<\/em><\/p>\n

Load data from external tables to regular tables<\/strong><\/p>\n

The following image shows you how to load data from an external table to a
\nregular table:<\/p>\n

\"oracle<\/p>\n

Image Source<\/em><\/a><\/p>\n

Conclusion<\/strong><\/p>\n

External tables enable you to access data from external sources as if it were in a table in the database. If users provide data in a spreadsheet or CSV format, and you need to update database tables based on the Excel sheet, an external table comes in handy.<\/p>\n","protected":false},"excerpt":{"rendered":"

Use Oracle external tables<\/p>\n","protected":false},"author":2,"featured_media":7633,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26458","content_type":"blog","created_by":"10130","authored_on":"2024-01-08T09:54:59+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Vikas Kumar, Oracle Database Administrator, Rackspace Technology","seo_description":"Query data from external files using Oracle external tables and leverage the power of SQL queries for seamless data retrieval and analysis.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26458-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2020-02-28T09:54:59","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Oracle External Tables Flat Files Query | Rackspace Technology","field_topics":"","field_slug":"use-oracle-external-tables","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7631","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7631"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7631\/revisions"}],"predecessor-version":[{"id":9573,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7631\/revisions\/9573"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7633"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}