ORA-30657: operation not supported on external organized table

要更新一個使用者資訊表的mail欄位時，提示ORA-30657錯誤

SQL> update class_users set email='XXXXXXXXX@qq.com' where id = 73257;
update class_users set email='XXXXXXXXX@qq.com' where id = 73257
       *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

此錯誤是因為class_users表是一個外部表格，不能直接update更新，可以直接更新引用的文字檔裡的內容

SQL> select object_name,object_type from user_objects where object_name='CLASS_USERS';

OBJECT_NAME                         OBJECT_TYPE
----------------------------------- -------------------
CLASS_USERS                         TABLE

SQL> select * from tab where tname='CLASS_USERS';
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CLASS_USERS                    TABLE

SQL> set long 9999999   
SQL> select dbms_metadata.get_ddl('TABLE','CLASS_USERS') from dual;     

DBMS_METADATA.GET_DDL('TABLE','CLASS_USERS')
--------------------------------------------------------------------------------

  CREATE TABLE "NEWCOURSE"."CLASS_USERS"
   (    "ID" NUMBER(10,0),
        "USER_NAME" VARCHAR2(150),
        "PASSWD" VARCHAR2(32),
        "CHANNEL" VARCHAR2(50),
        "PROPERTIES" NUMBER(19,0),
        "EMAIL" VARCHAR2(250),
        "MOBILE" VARCHAR2(20),
        "EMAIL_VERIFIED" NUMBER(1,0),
        "MOBILE_VERIFIED" NUMBER(1,0),
        "ROLE" NUMBER(1,0)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DPDIR"
      ACCESS PARAMETERS
      ( records delimited by newline
          fields terminated by ','
           )
      LOCATION
       ( 'class_users.info'
       )
    )
   REJECT LIMIT UNLIMITED

SQL> select directory_path from dba_directories where directory_name='DPDIR';

DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------
/tol/backup/dmp

QL> col email for a35
SQL> select id,email from class_users where id = 73257;

        ID EMAIL
---------- -----------------------------------
     73257

SQL> desc class_users;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 USER_NAME                                          VARCHAR2(150)
 PASSWD                                             VARCHAR2(32)
 CHANNEL                                            VARCHAR2(50)
 PROPERTIES                                         NUMBER(19)
 EMAIL                                              VARCHAR2(250)
 MOBILE                                             VARCHAR2(20)
 EMAIL_VERIFIED                                     NUMBER(1)
 MOBILE_VERIFIED                                    NUMBER(1)
 ROLE                                               NUMBER(1)
 

+++編輯/tol/backup/dmp 目錄下的class_users.info檔案，尋找到id=73257的記錄，改為：
73257,amosquito6,63c4d05e73b829727c1ff47bd4388d00,class,0,345305538@qq.com,,0,0,0

+++重新查詢：
SQL> select id,email from class_users where id = 73257;

        ID EMAIL
---------- -----------------------------------
     73257 345305538@qq.com