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,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