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 Go to next message
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 Go to previous message
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 Wink

Thx
Previous Topic: ORA-1840
Next Topic: Return all errors on a screen from exception
Goto Forum:
  


Current Time: Mon Dec 05 04:32:18 CST 2016

Total time taken to generate the page: 0.10711 seconds