• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4108
  • Last Modified:

Oracle 10i: Create External Table causing a ORA 30657 error

Hi,

I am moving my system from a 9i to a 10i Oracle database. When I try and create an external table I get the following error message:

ORA-30657: operation not supported on external organized table

I've created the directory:

CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'D:\temp';

and then tried the following:

CREATE TABLE EXTERNAL_DP_FG
(
  REGION                     VARCHAR2(4 BYTE),
  END_MARKET                 VARCHAR2(6 BYTE)
 
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXTRACT_DIR
     ACCESS PARAMETERS
       ( fields terminated by ';'
                   missing field values are null
                                  (
                                    REGION                          position(1: 4),
                                    END_MARKET                   position(6: 11)
                        

                                  )
                             )
     LOCATION (EXTRACT_DIR:'otifedpadp_FG.dat')
  )
REJECT LIMIT 0
LOGGING
NOCACHE
NOPARALLEL;

its all in the same schema so I dont need to grant read/write on the directory.

Any ideas?
0
joehodge
Asked:
joehodge
1 Solution
 
plamen73Commented:
try removing logging and/or nocache clauses.

As external table can not be modified, logging does not make sense here.
cache/nocache is relative to the DB block(s) read by oracle when selecting from the table. This also seems to be quite away from the external tables
0
 
paquicubaCommented:
CREATE TABLE EXTERNAL_DP_FG
(
  REGION                     VARCHAR2(4),     /* *****  BYTE is the default ***** */  
  END_MARKET                 VARCHAR2(6)  /* *****  BYTE is the default ***** */  
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXTRACT_DIR
     ACCESS PARAMETERS
       ( fields terminated by ';'
                 missing field values are null
                             (
                               REGION                       position(1: 4),
                               END_MARKET                 position(6: 11))
                          )
     LOCATION ('otifedpadp_FG.dat')  /* ******************* Try removing the Directory ********* */
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now