Which line of the command would cause an error?

Examine the following command to create an external table from EMPLOYEES and
DEPARTMENTS database tables.
1. CREATE TABLE employee_ext
2. (employee_id, first_name, department_name)
3. ORGANIZATION EXTERNAL
4. (
5. TYPE ORACLE_LOADER
6. DEFAULT DIRECTORY ext_dir
7. LOCATION (’emp1.dmp’)
8. )

9. PARALLEL
10. AS
11. SELECT e.emplyee_id, e.first_name, e.last_name, d.department_name
12. FROM employees e, departments d;
Which line of the command would cause an error?

A.
line 2, because the column names have been specified

B.
line 7, because file name must have a .dat extension

C.
line 3, because ORGANIZATION EXTERNAL has been specified

D.
line 9, because the PARALLEL option cannot be specified with one file name

E.
line 12, because there is no join defined between EMPLOYEES and DEPARTMENTS table
F. line 5, because ORACLE_LOADER is not a valid structure for external table creation with the
SELECT statement

Explanation:
External tables are created using the SQL CREATE TABLE…ORGANIZATION
EXTERNAL statement. When you create an external table, you specify the followingattributes:
1. TYPE – specifies the type of external table. The two available types are the ORACLE_LOADER
type and the ORACLE_DATAPUMP type. Each type of external table is supported by its own
access driver.
* The ORACLE_LOADER access driver is the default. It can perform only data loads, and the data
must comefrom text datafiles. Loads from external tables to internal tables are done by reading
from the external tables’ text-only datafiles .
* The ORACLE_DATAPUMP access driver can perform both loads and unloads. The data must
come from binary dump files. Loads to internal tables from external tables are done by fetching
from the binary dump files. Unloads from internal tables to external tables are done by populating
the external tables’ binary dump files.
2. DEFAULT DIRECTORY – specifies the default location of files that are read or written by
external tables. The location is specified with a directory object, not a directory path.
3. ACCESS PARAMETERS – describe the external data source and implements the type of
external table that was specified. Each type of external table has its own access driver that
provides access parameters unique to that type of external table.
4. LOCATION – specifies the location of the external data. The location is specified as a list of
directory objects and filenames. If the directory object is not specified, then the default directory
object is used as the file location.
REF.: Oracle(r) Database 10g Utilities, 12-2