Data Type Conversion During External Table Use

When data is moved into or out of an external table, it is possible that the same column will have a different data type in each of the following three places:

• The database: This is the source when data is unloaded into an external table and it is the destination when data is loaded from an external table.

• The external table: When data is unloaded into an external table, the data from the database is converted, if necessary, to match the data type of the column in the external table. Also, you can apply SQL operators to the source data to change its data type before the data gets moved to the external table. Similarly, when loading from the external table into a database, the data from the external table is automatically converted to match the data type of the column in the database. Again, you can perform other conversions by using SQL operators in the SQL statement that is selecting from the external table. For better performance, the data types in the external table should match those in the database.

• The data file: When you unload data into an external table, the data types for fields in the data file exactly match the data types of fields in the external table. However, when you load data from the external table, the data types in the data file may not match the data types in the external table. In this case, the data from the data file is converted to match the data types of the external table. If there is an error converting a column, then the record containing that column is not loaded. For better performance, the data types in the data file should match the data types in the external table.

Any conversion errors that occur between the data file and the external table cause the row with the error to be ignored. Any errors between the external table and the column in the database (including conversion errors and constraint violations) cause the entire operation to terminate unsuccessfully.

When data is unloaded into an external table, data conversion occurs if the data type of a column in the source table does not match the data type of the column in the external table. If a conversion error occurs, then the data file may not contain all the rows that were processed up to that point and the data file will not be readable. To avoid problems with conversion errors causing the operation to fail, the data type of the column in the external table should match the data type of the column in the database. This is not always possible, because external tables do not support all data types. In these cases, the unsupported data types in the source table must be converted into a data type that the external table can support. For example, if a source table named LONG_TAB has a LONG column, then the corresponding column in the external table being created, LONG_TAB_XT, must be a CLOB and the SELECT subquery that is used to populate the external table must use the TO_LOB operator to load the column:

CREATE TABLE LONG_TAB_XT (LONG_COL CLOB) ORGANIZATION EXTERNAL...SELECT TO_LOB(LONG_COL) FROM LONG_TAB;