Home » SQL & PL/SQL » SQL & PL/SQL » How to convert Varchar2 to CLOB (split from hijacked thread)
How to convert Varchar2 to CLOB (split from hijacked thread) [message #394323] |
Thu, 26 March 2009 08:13  |
m2c_analyst
Messages: 4 Registered: March 2009
|
Junior Member |
|
|
Hi all,
I have a problem, I am trying to redefine a table to modify a column (varchar 2 to clob).
I am using this code:
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('USR_TICKET','INC_TICKET',DBMS_REDEFINITION.cons_use_rowid);
CREATE TABLE INC_TICKET2
(
TIC_ID NUMBER NOT NULL,
TIC_FCREACION DATE,
TIC_FMODIFICACION DATE,
TIC_DESCRIPCION VARCHAR2(1000 BYTE),
TIC_SOLUCION NUMBER,
TIC_CIUDADANO NUMBER,
TIC_ESTADO NUMBER,
TIC_RESPUESTA CLOB,
TIC_CONTADOR NUMBER,
TIC_CREADOR VARCHAR2(255 BYTE),
TIC_ORIGEN NUMBER(1),
TIC_CONVERTIDA_FAQ NUMBER DEFAULT 0,
TIC_NUMACCESOS NUMBER DEFAULT 0,
TIC_MINISTERIO NUMBER,
TIC_ORGANISMO NUMBER
)
TABLESPACE TBTICKET
/
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(usr,tb1,tb2,"TIC_ID TIC_ID,TIC_FCREACION FCREACION,
TIC_FMODIFICACION TIC_FMODIFICACION, TIC_DESCRIPCION TIC_DESCRIPCION, TIC_SOLUCION TIC_SOLUCION, TIC_CIUDADANO TIC_CIUDADANO,
TIC_ESTADO TIC_ESTADO,TO_CLOB(TIC_RESPUESTA) TIC_RESPUESTA,TIC_CONTADOR TIC_CONTADOR,TIC_CREADOR TIC_CREADOR, TIC_ORIGEN TIC_ORIGEN,
TIC_CONVERTIDA_FAQ TIC_CONVERTIDA_FAQ, TIC_NUMACCESOS TIC_NUMACCESOS, TIC_MINISTERIO TIC_MINISTERIO, TIC_ORGANISMO TIC_ORGANISMO ",dbms_redefinition."cons_use_rowid" );
EXEC dbms_redefinition.copy_table_dependents(usr, tb1, tb2,
1, true,true,true,false,
error_count);
EXEC dbms_output.put_line('errors := ' || to_char(error_count));
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(usr, tb1, tb2);
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(usr, tb1, tb2);
END;
/
and I have getting this error in line 1:
ORA-00972: identificador demasiado largo (Identifier too long)
Looking for a solution , I have find that this error occurs when table name is longer than 30 characters , and my table name is 10. So I have no idea how to solve this....
Any idea?
Thanks a lot.
Ade
|
|
|
Re: How to convert Varchar2 to CLOB [message #394331 is a reply to message #394323] |
Thu, 26 March 2009 08:26  |
m2c_analyst
Messages: 4 Registered: March 2009
|
Junior Member |
|
|
Hi again,
I have decide use this other code:
alter table usr_ticket.inc_ticket add (TIC_RESPUESTA2 CLOB);
UPDATE USR_TICKET.INC_TICKET SET TIC_RESPUESTA2 = TIC_RESPUESTA;
ALTER TABLE USR_TICKET.INC_TICKET DROP COLUMN TIC_RESPUESTA;
ALTER TABLE USR_TICKET.INC_TICKET RENAME COLUMN TIC_RESPUESTA2 TO TIC_RESPUESTA;
It works so as we say "It it works do not touch it" ... or something like that 
Thx
|
|
|
Goto Forum:
Current Time: Mon Feb 17 21:28:20 CST 2025
|