Error Ora-22858 Invalid Alteration Of Datatype
Contents |
updated: September 23, 2013 - 5:32 pm UTC Category: Database – Version: 10g Latest Followup You Asked Hi Tom, Apologies if this has been answered elsewhere. I alter table add clob column oracle did do a search but didn't find any relevant information. I have how to convert varchar2 to clob in oracle a table that has been defined with a column of type VARCHAR2(4000). We are now looking to change ora-22859: invalid modification of columns this to a CLOB b'cos we need to store more data in it. I tried a straight forward ALTER TABLE and ended up with an error SQL> alter table test oracle change column type from clob to varchar2 2 modify (value clob); modify (value clob) * ERROR at line 2: ORA-22858: invalid alteration of datatype The SQL ref doesn't mention anything about converting VARCHAR2 into CLOB. How would I then do this? Also, if I have stored procedures that use this column (insert/update/delete/query) how would changing it to CLOB affect these? Would they need a re-write to work with
Update Clob Column In Oracle 11g
dbms_lob instead? Thank you (P.S:- Cannot believe I actually got a chance to Submit a question to you. Woohooo!) and we said... Longs can be converted to clobs http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_long_lob.htm#sthref873 (but it requires a copy of the table be made under the covers) Note: The ALTER TABLE statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements. Your choices in this case will be: a) new column ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(4000) ); Table created. ops$tkyte%ORA10GR2> insert into t values ( 1, 'hello' ); 1 row created. ops$tkyte%ORA10GR2> ops$tkyte%ORA10GR2> alter table t modify( y clob ); alter table t modify( y clob ) * ERROR at line 1: ORA-22858: invalid alteration of datatype ops$tkyte%ORA10GR2> !oerr ora 22858 22858, 00000, "invalid alteration of datatype" // *Cause: An attempt was made to modify the column type to object, REF, // nested table, VARRAY or LOB type. // *Action: Create a new column of the desired type and copy the current // co
table datatype from VARCHAR2(4000) to CLOB If this is your first visit, be sure to check out the FAQ by clicking the link above. You
Oracle To_clob
may have to register before you can post: click the register link how to update clob column in oracle above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. insert clob data in oracle Results 1 to 2 of 2 Thread: Modify table datatype from VARCHAR2(4000) to CLOB Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1770086700346491686 Switch to Hybrid Mode Switch to Threaded Mode 07-28-2006,10:32 AM #1 dpitts1979 View Profile View Forum Posts Junior Member Join Date Nov 2005 Posts 10 Modify table datatype from VARCHAR2(4000) to CLOB Gurus, I'm trying to modify a datatype from varchar2(4000) to Clob in one of my tables and I'm getting the following error: alter table gls_attr modify (attr_value CLOB); http://www.dbasupport.com/forums/showthread.php?52268-Modify-table-datatype-from-VARCHAR2(4000)-to-CLOB ORA-22858: invalid alteration of datatype I'm on 10G, shouldn't this work? Reply With Quote 07-28-2006,12:20 PM #2 ebrian View Profile View Forum Posts Senior Member Join Date Apr 2006 Posts 377 If the VARCHAR2(4000) column is empty, then you can modify the column to LONG first and then to CLOB. Code: SQL> desc test_clob Name Null? Type ------------------------------------ -------- ----------------- A1 NUMBER A2 VARCHAR2(4000) SQL> alter table test_clob modify a2 long; Table altered. SQL> alter table test_clob modify a2 clob; Table altered. SQL> desc test_clob Name Null? Type ------------------------------------ -------- ----------------- A1 NUMBER A2 CLOB If there is data in the column, you can do the following: Code: SQL> create table test_clob (a1 number, a2 varchar2(4000)); Table created. SQL> insert into test_clob values (1, rpad('*',4000,'*')); 1 row created. SQL> alter table test_clob add (a2_clob clob); Table altered. SQL> update test_clob set a2_clob = a2; 1 row updated. SQL> alter table test_clob drop (a2); Table altered. SQL> alter table test_clob add (a2 clob); Table altered. SQL> update test_clob set a2 = a2_clob; 1 row updated. SQL> alter table test_clob drop (a2_c
be enough. But later you want to insert more than 4000B, it's time to http://hustatyova.blogspot.com/2012/10/oracle-how-to-change-datatype-varchar2.html change this datatype to CLOB. If you don't have data in the table, it's ideal situation and the solution is very easy: SQL> create table test http://mohammedfauzi.blogspot.com/2012/03/ora-22858-invalid-alteration-of.html ( 2 id number, 3 name varchar2(50), 4 comments varchar2(4000) 5 ); Table created. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME in oracle VARCHAR2(50) COMMENTS VARCHAR2(4000) Because there is not direct way, how to modify column from VARCHAR2 to CLOB, we need to modify column in two steps. Steps: - VARCHAR2 --> LONG - LONG --> CLOB SQL> alter table test modify comments LONG; Table altered. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID update clob column NUMBER NAME VARCHAR2(50) COMMENTS LONG SQL> alter table test modify comments CLOB; Table altered. SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS CLOB SQL> insert into test (id, name, comments) values (1,'name_1','comment_1'); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME COMMENTS ---------- --------------- ------------------------------ 1 name_1 comment_1 Not very ideal situation is, if you don't have empty table, in this case is not possible to modify column from VARCHAR2 to CLOB so easy: SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(50) COMMENTS VARCHAR2(4000) SQL> begin for i in 1 .. 100 loop insert into test ( id, name, comments ) values ( i, 'name_'||i, 'comments_'||i ); end loop; commit; end; / PL/SQL procedure successfully completed. SQL> select count(*) from test; COUNT(*) ---------- 100 As before, there isn't possible to modify directly from VARCHAR2 to CLOB: SQL> alter t
datatype, ORA-22858: invalid alteration of datatype When we try to modify a datatype from varchar2(400) to Clob in a Oracle table, we'll get the following error: The following steps is the work-around to solve this issue: ALTER TABLE SCHEMANAME.TABLENAME ADD NEWCOLUMN CLOB; UPDATE SCHEMANAME.TABLENAME SET NEWCOLUMN = OLDCOLUMN; ALTER TABLE SCHEMANAME.TABLENAME drop COLUMN OLDCOLUMN; ALTER TABLE SCHEMANAME.TABLENAME rename COLUMN NEWCOLUMN TO OLDCOLUMN; Posted by Fauzi at 5:37 AM No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Followers Blog Archive ► 2015 (2) ► November (2) ► 2014 (2) ► March (1) ► February (1) ► 2013 (12) ► November (3) ► May (4) ► April (4) ► February (1) ▼ 2012 (17) ► December (1) ► November (1) ► October (2) ► April (2) ▼ March (11) ORA-22858: invalid alteration of datatype, ORA-228... To Search for a text in All the files in a folder ... ORA-01461: can bind a LONG value only for insert i... List tables and their corresponding tablespace nam... Grants for a User in Oracle Database Oracle: Search for column names in Oracle DataBase... Oracle: How to get All Schemas size in Server FW: Allah calls to the home of peace Oracel: How to create Sequence & Trigger Online HTML and CSS Table Border Style Wizard Oracle SQL: How to find session that is blocking a... ► 2011 (11) ► June (2) ► May (6) ► April (1) ► February (1) ► January (1) ► 2010 (23) ► December (1) ► November (3) ► September (1) ► August (1) ► July (1) ► June (2) ► May (6) ► April (5) ► March (3) ► 2009 (68) ► December (3) ► November (4) ► October (5) ► September (1) ► August (10) ► July (14) ► June (8) ► May (11) ► April (1) ► March (5) ► February (5) ► January (1) ► 2008 (31) ► December (3) ► October (6) ► September (4) ► August (6) ► July (2) ► May (1) ► April (3) ► March (5) ►