Re: create table External Table (ORA9I) problem!

I would not try to run the script without knowing first what I am doing. Look up the DOC on ORACLE external table.

1. Like Tom indicated, with a privileged account, create a directory where the input files, log files, bad files will reside like:

CREATE OR REPLACE DIRECTORY admin

    AS 'd:\oracle\ExternalTables';

If you want them to be in separate folders, create separate directories.

2. Grant READ, WRITE access to these folders to the owner of the external table like:

GRANT READ ON DIRECTORY admin TO scott; GRANT WRITE ON DIRECTORY admin TO scott;

3. There might be a bug in the ORALCE DOC where you get the ET example from, though I am not 100% certain. This part

> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
> (
> deptno INTEGER EXTERNAL,
> dname CHAR,
> loc CHAR
> )

does not make sense to me, since they do not correspond to the column list of the external table. The specs of the field list are:

*If no datatype is specified for a field, it is assumed to be CHAR(1) for a nondelimited field, and CHAR(255)for a delimited field. *If no field list is specified, then the fields in the datafile are assumed to be in the same order as the fields in the external table. The datatype for all fields is CHAR(255). *If no field list is specified and no delim_spec clause is specified, then the fields in the datafile are assumed to be in the same order as fields in the external table. All fields are assumed to be CHAR(255) and terminated by a comma.

You can add the 3 fields to the field list, but I think you need to include in the list other fields that correspond to the table columns. Otherwise ORACLE do not know how to match the fields to the columns.

mawf_at_asiainfo.com (mawf) wrote in message news:<6c5a24c8.0208240723.16cd153c_at_posting.google.com>...
> i have found a demo doc details as below:
> External Table Example
> The following statement creates an external table that represents a
> subset
> of the sample hr.employees table. The opaque_format_spec is shown in
> italics.
> Please refer to Oracle9i Database Utilities for information on the
> ORACLE_LOADER
> access driver and how to specify values for the opaque_format_spec.
>
> CREATE TABLE emp_external (
> employee_id NUMBER(6),
> last_name VARCHAR2(20),
> email VARCHAR2(25),
> hire_date DATE,
> job_id VARCHAR2(10),
> salary NUMBER(8,2)
> )
> ORGANIZATION EXTERNAL
> (TYPE oracle_loader
> DEFAULT DIRECTORY admin
> ACCESS PARAMETERS
> (
> RECORDS DELIMITED BY newline
> BADFILE 'ulcase1.bad'
> DISCARDFILE 'ulcase1.dis'
> LOGFILE 'ulcase1.log'
> SKIP 20
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
> (
> deptno INTEGER EXTERNAL,
> dname CHAR,
> loc CHAR
> )
> )
> LOCATION ('ulcase1.dat')
> )
> REJECT LIMIT UNLIMITED;
>
> ---------------------------
> when i run the script , it pops a error msg just like 'cound not
> found DEFAULT DIRECTORY admin',so why ?
> and i am confused for those below:
> (
> deptno INTEGER EXTERNAL,
> dname CHAR,
> loc CHAR
> )
> what does this mean ?
>
> thanks a lot!
Received on Sun Aug 25 2002 - 13:17:44 CDT