下面的代码中,列出了两张表的表结构及其中一张表关于列ISEDIT的数据情况,当我把其中一张有数据的表的数据住另外一张表里插入时,报出了下面的错误!!!
SQL> DESC CLIENTINFO
Name             Type           Nullable Default Comments 
---------------- -------------- -------- ------- -------- 
CLIENTSHORT      VARCHAR2(25)   Y                         
CLIENTFULL       VARCHAR2(200)  Y                         
ADDRESS          VARCHAR2(200)  Y                         
BAIRRO           VARCHAR2(40)   Y                         
MUNICIPIO        VARCHAR2(60)   Y                         
UF               VARCHAR2(4)    Y                         
CEP              VARCHAR2(36)   Y                         
CGC              VARCHAR2(40)   Y                         
INSCRICAO        VARCHAR2(40)   Y                         
FONE             VARCHAR2(60)   Y                         
FAX              VARCHAR2(60)   Y                         
TRANSPORTADOR    VARCHAR2(50)   Y                         
TELDETRANS       VARCHAR2(40)   Y                         
CREDITO          VARCHAR2(10)   Y                         
DUPLICATA        CHAR(1)        Y        0                
SOCIO            VARCHAR2(225)  Y                         
ISEDIT           CHAR(1)        Y        0                
PAYEE            VARCHAR2(100)  Y                         
RETURNRECORD     VARCHAR2(40)   Y                         
ARQUIVOGERADO    CHAR(1)        Y        0                
INSCRICAOSUFRAMA VARCHAR2(30)   Y                         
ADVICEPAYEE      VARCHAR2(200)  Y                         
CONSULTA         VARCHAR2(2000) Y                         
INFORMACAO       VARCHAR2(2000) Y                         
 
SQL> DESC CLIENTINFO_NEW
Name             Type          Nullable Default Comments 
---------------- ------------- -------- ------- -------- 
CLIENTSHORT      VARCHAR2(25)  Y                         
CLIENTFULL       VARCHAR2(200) Y                         
ADDRESS          VARCHAR2(200) Y                         
BAIRRO           VARCHAR2(40)  Y                         
MUNICIPIO        VARCHAR2(60)  Y                         
UF               VARCHAR2(4)   Y                         
CEP              VARCHAR2(36)  Y                         
CGC              VARCHAR2(40)  Y                         
INSCRICAO        VARCHAR2(40)  Y                         
FONE             VARCHAR2(60)  Y                         
FAX              VARCHAR2(60)  Y                         
TRANSPORTADOR    VARCHAR2(50)  Y                         
TELDETRANS       VARCHAR2(40)  Y                         
CREDITO          VARCHAR2(10)  Y                         
DUPLICATA        VARCHAR2(5)   Y                         
SOCIO            VARCHAR2(225) Y                         
CONSULTA         CLOB          Y                         
INFORMACAO       CLOB          Y                         
ISEDIT           VARCHAR2(5)   Y                         
PAYEE            VARCHAR2(100) Y                         
RETURNRECORD     VARCHAR2(40)  Y                         
ARQUIVOGERADO    VARCHAR2(5)   Y                         
INSCRICAOSUFRAMA VARCHAR2(30)  Y                         
ADVICEPAYEE      VARCHAR2(200) Y                         
 
SQL> SELECT MAX(LENGTH(ISEDIT)) FROM CLIENTINFO_NEW;
 
MAX(LENGTH(ISEDIT))
-------------------
                  1
SQL> SELECT MAX(ISEDIT) FROM CLIENTINFO_NEW;
 
MAX(ISEDIT)
-----------
0
SQL> INSERT INTO CLIENTINFO VARLUES SELECT * FROM CLIENTINFO_NEW;
 
INSERT INTO CLIENTINFO VARLUES SELECT * FROM CLIENTINFO_NEW
 
ORA-12899: 列 "JIANG"."CLIENTINFO"."ISEDIT" 的值太大 (实际值: 80, 最大值: 1)
 

解决方案 »

  1.   

    CLIENTINFO 里的isedit是char(1),只能存一个字符,CLIENTINFO_NEW的isedit是varchar2(5),最大为5位,应该是有的数据大于等于两位,所以你没法插入到CLIENTINFO里去.
      

  2.   

    我才刚已经把CLIENTINFO_NEW的ISEDIT列更改为CHAR(1)类型了SQL> ALTER TABLE CLIENTINFO_NEW MODIFY "ISEDIT" CHAR(1) DEFAULT 0;
     
    Table altered
     
    SQL> DESC CLIENTINFO_NEW
    Name             Type          Nullable Default Comments 
    ---------------- ------------- -------- ------- -------- 
    CLIENTSHORT      VARCHAR2(25)  Y                         
    CLIENTFULL       VARCHAR2(200) Y                         
    ADDRESS          VARCHAR2(200) Y                         
    BAIRRO           VARCHAR2(40)  Y                         
    MUNICIPIO        VARCHAR2(60)  Y                         
    UF               VARCHAR2(4)   Y                         
    CEP              VARCHAR2(36)  Y                         
    CGC              VARCHAR2(40)  Y                         
    INSCRICAO        VARCHAR2(40)  Y                         
    FONE             VARCHAR2(60)  Y                         
    FAX              VARCHAR2(60)  Y                         
    TRANSPORTADOR    VARCHAR2(50)  Y                         
    TELDETRANS       VARCHAR2(40)  Y                         
    CREDITO          VARCHAR2(10)  Y                         
    DUPLICATA        CHAR(1)       Y        0                
    SOCIO            VARCHAR2(225) Y                         
    CONSULTA         CLOB          Y                         
    INFORMACAO       CLOB          Y                         
    ISEDIT           CHAR(1)       Y        0                
    PAYEE            VARCHAR2(100) Y                         
    RETURNRECORD     VARCHAR2(40)  Y                         
    ARQUIVOGERADO    CHAR(1)       Y        0                
    INSCRICAOSUFRAMA VARCHAR2(30)  Y                         
    ADVICEPAYEE      VARCHAR2(200) Y                         
     
    SQL> DESC CLIENTINFO
    Name             Type           Nullable Default Comments 
    ---------------- -------------- -------- ------- -------- 
    CLIENTSHORT      VARCHAR2(25)   Y                         
    CLIENTFULL       VARCHAR2(200)  Y                         
    ADDRESS          VARCHAR2(200)  Y                         
    BAIRRO           VARCHAR2(40)   Y                         
    MUNICIPIO        VARCHAR2(60)   Y                         
    UF               VARCHAR2(4)    Y                         
    CEP              VARCHAR2(36)   Y                         
    CGC              VARCHAR2(40)   Y                         
    INSCRICAO        VARCHAR2(40)   Y                         
    FONE             VARCHAR2(60)   Y                         
    FAX              VARCHAR2(60)   Y                         
    TRANSPORTADOR    VARCHAR2(50)   Y                         
    TELDETRANS       VARCHAR2(40)   Y                         
    CREDITO          VARCHAR2(10)   Y                         
    DUPLICATA        CHAR(1)        Y        0                
    SOCIO            VARCHAR2(225)  Y                         
    ISEDIT           CHAR(1)        Y        0                
    PAYEE            VARCHAR2(100)  Y                         
    RETURNRECORD     VARCHAR2(40)   Y                         
    ARQUIVOGERADO    CHAR(1)        Y        0                
    INSCRICAOSUFRAMA VARCHAR2(30)   Y                         
    ADVICEPAYEE      VARCHAR2(200)  Y                         
    CONSULTA         VARCHAR2(2000) Y                         
    INFORMACAO       VARCHAR2(2000) Y                         
     
    SQL> INSERT INTO CLIENTINFO VARLUES SELECT * FROM CLIENTINFO_NEW;
     
    INSERT INTO CLIENTINFO VARLUES SELECT * FROM CLIENTINFO_NEW
     
    ORA-12899: 列 "JIANG"."CLIENTINFO"."ISEDIT" 的值太大 (实际值: 80, 最大值: 1)
     
      

  3.   


    SQL> SELECT MAX(LENGTH(ISEDIT)) FROM CLIENTINFO_NEW;
     
    MAX(LENGTH(ISEDIT))
    -------------------
                      1
    SQL> SELECT MAX(ISEDIT) FROM CLIENTINFO_NEW;
     
    MAX(ISEDIT)
    -----------
    0
    这里已经显示CLIENTINFO_NEW的ISEDIT列值只有一位了
      

  4.   

    晕倒,才发现,你两个表的表结构都不一样,怎么直接就insert into select了.
    改成
    insert into CLIENTINFO(字段列表) select 字段列表 from CLIENTINFO_NEW 的形式.
      

  5.   

    呵呵,是的呀!
    因为CLIENTINFO_NEW表这样创建的CREATE TABLE CLIENTINFO_NEW AS SELECT * FROM CLIENTINFO;
    所以后面就直接写了...太粗心了!
      

  6.   

    首先呢, 采用insert into table1(字段) select * from table2 方式,table1表必须存在而且结构要跟table2一样!   前面CLIENTINFO表是char, CLIENTINFO_NEW是varchar2; 一个是存字符一个是存字符串。一个是固定长度一个是可变长度;      你用的是oracle吧! 
    VARCHAR2比CHAR节省空间,在效率上比CHAR会稍微差一些,即要想获得效率,就必须牺牲一定的空间,这也就是我们在数据库设计上常说的‘以空间换效率’!     由于char(1)所以会截取一个字符插入!   
      
    在mssql中可以用select into方式,会自动创建表结构和数据!  oracle里面相对的是
    create table tableb 
    as 

      select * from tablea    
    )
    试试希望对你有帮助!
       
      

  7.   

    TO:xmsw188
    之前已经经过贴了,还是很谢谢你的!