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?
joehodgeAsked:
Who is Participating?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.