Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION (Oracle8i Enterprise Edition Release 8.1.7.0.0 /windows7)
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #666413] Fri, 03 November 2017 05:42 Go to next message
shawaj
Messages: 50
Registered: January 2016
Member
I am trying to partitioning on existing table but i am getting error while going to alter table as mentioned below.
SQL> CREATE TABLE VOUCHER_CASH_BANK_#(
  2  SEQ_NO         NUMBER(10) NOT NULL,
  3  LOCNO          NUMBER(2) NOT NULL,
  4  CCODE          NUMBER(2) NOT NULL,
  5  DIV_CODE       NUMBER(2) NOT NULL,
  6  VR_TYPE_CODE   VARCHAR2(4) REFERENCES VR_TYPE,
  7  VR_DATE        DATE NOT NULL,
  8  VR_NO          VARCHAR2(20) NOT NULL,
  9  EXP_DATE       DATE ,
 10  AC_CODE5       VARCHAR2(20) NOT NULL,
 11  NARRATION      VARCHAR2(100),
 12  REMARKS        VARCHAR2(100),
 13  DB_CR_IND      VARCHAR2(2) NOT NULL,
 14  AMOUNT         NUMBER(13,2),
 15  BILLNO         VARCHAR2(20),
 16  BILL_DATE      DATE,
 17  CHEQUE_NO      NUMBER(10),
 18  CHEQUE_DATE    DATE ,
 19  CCSUBCODE      NUMBER(3),
 20  AC_CODE5_REIMB VARCHAR2(12),
 21  REIMB_CODE     NUMBER(2),
 22  REIMB_DESC_SNO NUMBER(2),
 23  EXP_DATE2      DATE ,
 24  TOTAL_AMOUNT   NUMBER(15,2),
 25  VALIDATION_NO  VARCHAR2(20),
 26  STFORM_CODE    NUMBER(2),
 27  STFORM_NO      NUMBER(10),
 28  IND_NO         VARCHAR2(20),
 29  ORD_NO         VARCHAR2(8),
 30  ITEM_CODE      NUMBER(5) ,
 31  QTY            NUMBER(13,2),
 32  INV_NO         VARCHAR2(16),
 33  INV_DATE       DATE ,
 34  BANK_REFNO     VARCHAR2(20),
 35  BANK_REFDT     DATE  ,
 36  INT_RATE       NUMBER(5,2),
 37  NO_DAYS        NUMBER(8,2) ,
 38  LC_NO          VARCHAR2(16),
 39  AMOUNT_FC      NUMBER(13,2),
 40  CURRENCY       NUMBER(2),
 41  CON_RATE       NUMBER(5,2),
 42  RATE           NUMBER(13,2),
 43  UM             NUMBER(3) ,
 44  IND            VARCHAR2(1),
 45  UCODE          NUMBER(4) ,
 46  ENTRY_DATE     DATE ,
 47  AWB_NO         VARCHAR2(25),
 48  AWB_DATE       DATE ,
 49  RECO_IND       VARCHAR2(2))
 50  PARTITION BY RANGE(VR_DATE)
 51  (PARTITION my_PARTITION VALUES LESS THAN (MAXVALUE));
 
Table created
 
SQL> 
SQL> ALTER TABLE VOUCHER_CASH_BANK_#
  2    EXCHANGE PARTITION my_PARTITION
  3    WITH TABLE VOUCHER_CASH_BANk
  4    WITHOUT VALIDATION;
 
ALTER TABLE VOUCHER_CASH_BANK_#
  EXCHANGE PARTITION my_PARTITION
  WITH TABLE VOUCHER_CASH_BANk
  WITHOUT VALIDATION
 
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
 
SQL> DESC VOUCHER_CASH_BANK;
Name           Type          Nullable Default Comments                                                    
-------------- ------------- -------- ------- ----------------------------------------------------------- 
SEQ_NO         NUMBER(10)                                                                                 
LOCNO          NUMBER(2)                                                                                  
CCODE          NUMBER(2)                                                                                  
DIV_CODE       NUMBER(2)                                                                                  
VR_TYPE_CODE   VARCHAR2(4)                                                                                
VR_DATE        DATE                                                                                       
VR_NO          VARCHAR2(20)                                                                               
EXP_DATE       DATE          Y                                                                            
AC_CODE5       VARCHAR2(20)                                                                               
NARRATION      VARCHAR2(100) Y                                                                            
REMARKS        VARCHAR2(100) Y                                                                            
DB_CR_IND      VARCHAR2(2)                                                                                
AMOUNT         NUMBER(13,2)  Y                                                                            
BILLNO         VARCHAR2(20)  Y                                                                            
BILL_DATE      DATE          Y                                                                            
CHEQUE_NO      NUMBER(10)    Y                                                                            
CHEQUE_DATE    DATE          Y                                                                            
CCSUBCODE      NUMBER(3)     Y                                                                            
AC_CODE5_REIMB VARCHAR2(12)  Y                                                                            
REIMB_CODE     NUMBER(2)     Y                                                                            
REIMB_DESC_SNO NUMBER(2)     Y                                                                            
EXP_DATE2      DATE          Y                                                                            
TOTAL_AMOUNT   NUMBER(15,2)  Y                                                                            
VALIDATION_NO  VARCHAR2(20)  Y                                                                            
STFORM_CODE    NUMBER(2)     Y                                                                            
STFORM_NO      NUMBER(10)    Y                bill no settlemnt entry                                     
IND_NO         VARCHAR2(20)  Y                                                                            
ORD_NO         VARCHAR2(8)   Y                                                                            
ITEM_CODE      NUMBER(5)     Y                                                                            
QTY            NUMBER(13,2)  Y                                                                            
INV_NO         VARCHAR2(16)  Y                                                                            
INV_DATE       DATE          Y                                                                            
BANK_REFNO     VARCHAR2(20)  Y                                                                            
BANK_REFDT     DATE          Y                                                                            
INT_RATE       NUMBER(5,2)   Y                                                                            
NO_DAYS        NUMBER(8,2)   Y                                                                            
LC_NO          VARCHAR2(16)  Y                                                                            
AMOUNT_FC      NUMBER(13,2)  Y                                                                            
CURRENCY       NUMBER(2)     Y                                                                            
CON_RATE       NUMBER(5,2)   Y                                                                            
RATE           NUMBER(13,2)  Y                                                                            
UM             NUMBER(3)     Y                                                                            
IND            VARCHAR2(1)   Y                                                                            
UCODE          NUMBER(4)     Y                                                                            
ENTRY_DATE     DATE          Y                                                                            
AWB_NO         VARCHAR2(25)  Y                                                                            
AWB_DATE       DATE          Y                                                                            
RECO_IND       VARCHAR2(2)   Y                for bank reco and for purchase type from purchase form(1,2) 
Please help me .
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #666439 is a reply to message #666413] Fri, 03 November 2017 17:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
VOUCHER_CASH_BANK MODIFY.VR_DATE must also be not null. Please see the reproduction of problem and solution below.

-- reproduction of problem:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE VOUCHER_CASH_BANK_#(VR_DATE DATE NOT NULL)
  2  PARTITION BY RANGE(VR_DATE)
  3    (PARTITION my_PARTITION VALUES LESS THAN (MAXVALUE))
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO  VOUCHER_CASH_BANK_# VALUES (SYSDATE)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE VOUCHER_CASH_BANK(VR_DATE DATE)
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO  VOUCHER_CASH_BANK VALUES (SYSDATE)
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE VOUCHER_CASH_BANK_#
  2    EXCHANGE PARTITION my_PARTITION
  3    WITH TABLE VOUCHER_CASH_BANK
  4    WITHOUT VALIDATION
  5  /
ALTER TABLE VOUCHER_CASH_BANK_#
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

-- solution:
SCOTT@orcl_12.1.0.2.0> ALTER TABLE VOUCHER_CASH_BANK MODIFY (VR_DATE DATE NOT NULL)
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> ALTER TABLE VOUCHER_CASH_BANK_#
  2    EXCHANGE PARTITION my_PARTITION
  3    WITH TABLE VOUCHER_CASH_BANK
  4    WITHOUT VALIDATION
  5  /

Table altered.
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #666440 is a reply to message #666439] Fri, 03 November 2017 22:32 Go to previous messageGo to next message
shawaj
Messages: 50
Registered: January 2016
Member
Thank you so much Mr.Barbara Boehmer for your helpful reply.
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #666442 is a reply to message #666440] Sat, 04 November 2017 03:40 Go to previous message
Littlefoot
Messages: 21238
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
(Barbara is a female)
Previous Topic: TABLE PARTITIONING
Next Topic: Prevent deletion
Goto Forum:
  


Current Time: Tue Nov 21 04:22:36 CST 2017

Total time taken to generate the page: 0.02610 seconds