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.<\/p>\n
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.<\/p>\n
There are steps to get data to DB with external table.<\/p>\n
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.<\/p>\n
SQL> CREATE DIRECTORY ALERT_DIR AS '\/oracle\/diag\/rdbms\/onurdb\/ONURDB\/trace';\r\n\t\r\nDirectory created.\r\n<\/pre>\n2. Then an External Table is created where we will save the alert logs.<\/p>\n
\tCREATE TABLE OARDAHANLI.EXT_TBL_TST\r\n\t\t(\r\n\t\tID NUMBER,\r\n\t\tISIM VARCHAR2 (4),\r\n\t\tTARIH DATE\r\n\t\t)\r\n\t\tORGANIZATION EXTERNAL\r\n\t\t(\r\n\t\tTYPE ORACLE_LOADER\r\n\t\tDEFAULT DIRECTORY REPLAY_DIR\r\n\t\tACCESS PARAMETERS\r\n\t\t(\r\n\t\tRECORDS DELIMITED BY '\\r\\n'\r\n\t\tNOBADFILE\r\n\t\tNOLOGFILE\r\n\t\tNODISCARDFILE\r\n\t\tFIELDS TERMINATED BY ','\r\n\t\t missing field values are null\r\n\t (\r\n\t \"ID\",\r\n\t \"ISIM\",\r\n\t \"TARIH\" DATE \"DD.MM.YYYY HH24:MI:SS\"\r\n\t )\r\n\t\t)\r\n\t\tLOCATION ('ext_tbl.txt')\r\n\t\t)\r\n\tREJECT LIMIT UNLIMITED;\r\n\t\r\n\tTable created.\r\n<\/pre>\n3. When the query is made, we see that the information comes.<\/p>\n
\tSQL> SELECT * FROM ALERT_LOG_FILE;\r\n\tALERT\r\n\t--------------------------------------------------------------------------------\r\n\tORACLE_HOME = \/oracle\/ora11g\r\n\tORACLE_BASE from environment = \/oracle\r\n\tORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...\r\n\tORACLE_HOME = \/oracle\/ora11g\r\n\tORACLE_BASE from environment = \/oracle\r\n\tORA-00313: open failed for members of log group 1 of thread 1\r\n\tORA-00312: online log 1 thread 1: '\/oracle\/ora11g\/data_ONURDB\/ONURDB\/redo01.log'\r\n\tORA-27037: unable to obtain file status\r\n\tORA-00313: open failed for members of log group 1 of thread 1\r\n\tORA-00312: online log 1 thread 1: '\/oracle\/ora11g\/data_ONURDB\/ONURDB\/redo01.log'\r\n\tORA-27037: unable to obtain file status\r\n<\/pre>\nDescriptions of the access parameters we use when creating the External Table are as follows.<\/p>\n
DELIMITED BY:<\/strong> Specifies the character by which the fields will be separated.<\/p>\n
TERMINATED BY:<\/strong> Specifies how the fields are terminated.<\/p>\n
FIXED:<\/strong> Specifies that the record size will be a fixed width.<\/p>\n
BADFILE:<\/strong> This is the file where records that cannot be loaded due to a problem are kept.<\/p>\n
NOBADFILE:<\/strong> Badfile file is not created.<\/p>\n
LOGFILE:<\/strong> This is the log file where the messages received while creating the external table are kept.<\/p>\n
DISCARDFILE:<\/strong> If an error occurs while loading records that meet the criteria specified with the word LOAD WHEN, they are saved in this file.<\/p>\n
NODISCARDFILE:<\/strong> Discardfile file is not used.<\/p>\n
SKIP:<\/strong> The specified number of records are skipped during loading.<\/p>\n
MISSING FIELD VALUES ARE NULL:<\/strong> Also loads fields that do not contain data, such as a NULL value.<\/p>\n
<\/div>\n