Friday , June 21 2024

Creating An Oracle External Table

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.

2. Then an External Table is created where we will save the alert logs.

3. When the query is made, we see that the information comes.

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.



Leave a Reply

Your email address will not be published. Required fields are marked *