External Tables in Oracle

External table:-

Scenario:-

Your requirement is to get data from external files which should look like a table to the user.

Note: External files are situated outside the database but appear as if it is located in the database.

What external table is?

External table enables you to access data in external sources as if it were in a table in the database.

There are some restrictions while using external tables. As external tables can be queried, they're not usable in many ways like regular oracle tables. You cannot perform any DML operations on external tables other than table creation; drawback of external table is you can't create an index on an external table. External tables are largely used as a convenient way of moving data into and out of the database.

Let’s start creating external table.

You will need the oracle directory first so let’s create it.

CREATE OR REPLACE DIRECTORY TEST AS ‘F: \CHANCHAL\’;

Grant read and writes privileges to required users.

GRANT READ, WRITE ON TEST TO USER_NAME;

Now we are ready to create a table. Create a table like shown below:

Directory name is TEST and file name is TEST.CSV:-

Note: - While creating a table there is a parameter before ‘Default Directory ’, “TYPE ORACLE_LOADER”. When you do not specify there, oracle will by default assume that type is oracle_loader.

Now we can select data from TEST table:

Data won’t be present in the table until and unless you select it. So let’s query on the table:-

By default, a log of load operations is created in the same windows directory as the load files, but this can be changed using the LOGFILE parameter.

Any rows that fail to load are written to a bad file. By default, the bad file is created in the same directory as the load files, but this can be changed using the BADFILE parameter.

In access parameter you can add bad file name and log file name as shown below:-

ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE

    BADFILE BDUMP:'READ_ALERT_%A_%P.BAD'

    LOGFILE BDUMP:'READ_ALERT_%A_%P.LOG'

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    (

      LINE  CHAR (4000)

    )

What is the difference between sql * loader and external tables:-

While using sql * loader we can perform DML operations on one table, but in case of external tables we cannot perform DML operations. We also cannot create index on external tables nor can create constraints. Let’s have an example:-

If you want to perform DML operations on the table which is created while using sql * loader, you can do it like normal DML operations.

an><� � > p ��W HEY style='font-size:11.0pt;font-family:"Arial","sans-serif"'>END IF

Below is the example with conditional statement which shows the maximum number in the given set of numbers:-

Let’s see another example which will calculate your net salary:-

Using Sql*Loader