It is possible to select directly from the file with the use of EXTERNAL without actually creating an external table in the data dictionary. This allows for external data to be part of a subquery, virtual view, or another transformation type of process.
Here is an example of how this works:
SELECT columns FROM EXTERNAL ((column definitions) TYPE [ access_driver_ type] external_table_properties [REJECT LIMIT clause])
SQL> select first_name, last_name, hiredate, department_name from external ( (first _name varchar2(50), last_name varchar2(50), hiredate date, department_name varchar2(50)) type oracle_loader default directory ext_data access parameters ( records delimited by newline nobadfile nologfile fields date_format date mask “mm/dd/yyyy”) location (’empbydep.csv’) reject limit unlimited) empbydep_external department=’HR’;
The empbydep_external table is not actually created as an external table, and this data is available to query and specify any of the columns or filter by a different selection in the WHERE CLAUSE. This is also possible with JSON and useful when accessing data APIs that are provided in the JSON format. This does not load the data into the table but can be queried and used in several different methods for views and reference data that is available by API to complete data sets in data integrations.
Unloading and Loading Data Using an External Table External tables can also be used to select data from a regular database table and create a binary dump file. This is known as unloading data.
The advantage of this technique is that the dump file is platform-independent and can be used to move large amounts of data between servers of different platforms.
You can also encrypt or compress data, or both, when creating the dump file. Doing so provides you with an efficient and secure way of transporting databases between database servers.
A small example here illustrates the technique of using an external table to unload data. Here are the steps required:
1. Create a directory object that specifies where you want the dump file placed on disk. Again, grant read and write access to the directory object for the user that needs access.
2. Use the CREATE TABLE…ORGANIZATION EXTERNAL…AS SELECT statement to unload data from the database into the dump file.
First, create a directory object. The next bit of code creates a directory object, named DP, that points to the /oradump directory:
SQL> create directory dp as ‘/oradump’;
SQL> grant read, write on directory dp to larry;
This example depends on a table named INV; for reference, here is the DDL for the INV table:
SQL> create table inv (inv_id number, inv_desc varchar2(30));
To create a dump file, use the ORACLE_DATAPUMP access driver of the CREATE TABLE…ORGANIZATION EXTERNAL statement. This example unloads the INV table’s contents into the inv.dmp file:
SQL> create table inv_ext Organization external ( Type oracle_datapump Default directory dp Location (‘inv.dmp’)) as select * from inv;
The previous command creates two things:
• An external table name INV_EXT, based on the structure and data within the INV table
• A platform-independent dump file named inv.dmp
Now, you can copy the inv.dmp file to a separate database server and base an external table on this dump file. The remote server (to which you copy the dump file) can be a platform different from the server on which you created the file.
For example, you can create a dump file on a Windows server, copy to a Linux server, and select from the dump file via an external table.
In this example, the external table is named INV_DW:
SQL> create table inv_dw (inv_id number, inv_desc varchar2(30)) organization external ( type oracle_datapump default directory dp location (‘inv.dmp’));
After it’s created, you can access the external table data from SQL*Plus:
SQL> select * from inv_dw;
You can also create and load data into regular tables, using the dump file:
SQL> create table inv as select * from inv_dw;
This provides a simple and efficient mechanism for transporting data from one platform to another.
Leave a Reply