In this tutorial, you will learn about Oracle external tables that allow you to access data in flat files as if it were in tables.
The data in the .csv extension files on the operating system may need to be queried by the database or transferred to a table in our database. In this case, we can use external table.
There are steps to get data to DB with external table.
1. A database directory is created for the directory where the .csv extension or any other file is located. Database directory is a directory created in our database so that a directory on the operating system can be used by the database side.
|
1 2 3 |
SQL> CREATE DIRECTORY ALERT_DIR AS '/oracle/diag/rdbms/onurdb/ONURDB/trace'; Directory created. |
2. Then an External Table is created where we will save the alert logs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE TABLE OARDAHANLI.EXT_TBL_TST ( ID NUMBER, ISIM VARCHAR2 (4), TARIH DATE ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY REPLAY_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY '\r\n' NOBADFILE NOLOGFILE NODISCARDFILE FIELDS TERMINATED BY ',' missing field values are null ( "ID", "ISIM", "TARIH" DATE "DD.MM.YYYY HH24:MI:SS" ) ) LOCATION ('ext_tbl.txt') ) REJECT LIMIT UNLIMITED; Table created. |
3. When the query is made, we see that the information comes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> SELECT * FROM ALERT_LOG_FILE; ALERT -------------------------------------------------------------------------------- ORACLE_HOME = /oracle/ora11g ORACLE_BASE from environment = /oracle ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL... ORACLE_HOME = /oracle/ora11g ORACLE_BASE from environment = /oracle ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/oracle/ora11g/data_ONURDB/ONURDB/redo01.log' ORA-27037: unable to obtain file status ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/oracle/ora11g/data_ONURDB/ONURDB/redo01.log' ORA-27037: unable to obtain file status |
Descriptions of the access parameters we use when creating the External Table are as follows.
DELIMITED BY: Specifies the character by which the fields will be separated.
TERMINATED BY: Specifies how the fields are terminated.
FIXED: Specifies that the record size will be a fixed width.
BADFILE: This is the file where records that cannot be loaded due to a problem are kept.
NOBADFILE: Badfile file is not created.
LOGFILE: This is the log file where the messages received while creating the external table are kept.
DISCARDFILE: If an error occurs while loading records that meet the criteria specified with the word LOAD WHEN, they are saved in this file.
NODISCARDFILE: Discardfile file is not used.
SKIP: The specified number of records are skipped during loading.
MISSING FIELD VALUES ARE NULL: Also loads fields that do not contain data, such as a NULL value.
