Varchar2 field converted to lob type

Summary: There are two ways to convert the varchar2 of a field to the lob type.

 

Method 1: Put the data in the varchar2 column into the middle column by means of the temporary intermediate column, clear the varchar2 column, convert it to the long type, and then convert it from the Long type to the clob type, put the data in the middle column in The clob column and delete the temporary middle column;

The second method is online definition. The internal method is the same. The middle table is used to redefine the columns in the table;

Varchar2 ---- long ----- clob

Create Table TT1 (A varchar2 (10), N number );
Insert into TT1 values ('A', 1 );
Insert into TT1 values ('bb', 2 );
Insert into TT1 values ('cc', 3 );
Commit;

SQL> select * from TT1;

A N
--------------------
Aa 1
Bb 2
CC 3

SQL> DESC TT1;
Name null? Type
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A varchar2 (10)
N number

First try to convert from varchar2 to lob type, no

SQL> ALTER TABLE TT1 modify a long;
Alter table TT1 modify a long
*
Error at line 1:
ORA-01439: column to be modified must be empty to change datatype

 

Data in the table cannot be converted from varchar2 to long.

SQL> ALTER TABLE T1 modify a long;
Alter table T1 modify a long
*
Error at line 1:
ORA-01439: column to be modified must be empty to change datatype

 

 

Alter table TT1 add B varchar2 (10 );

 

SQL> Update TT1 Set B =;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from TT1;

A n B
------------------------------
Aa 1 aa
Bb 2 bb
CC 3 cc

The data in the empty table is converted from varchar2 to long.

Update TT1 set a = '';

Data in the table cannot be converted from varchar2 to long.

Alter table TT1 modify a long;
Alter table TT1 modfify A clob;
Update TT1 Set A = B;

SQL> select * from TT1;

A n B
----------------------------------------------------------------------------------------------------
Aa 1 aa
Bb 2 bb
CC 3 cc

SQL> DESC TT1
Name null? Type
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A clob
N number
B varchar2 (10)

SQL> alter table TT1 drop Column B;

Table altered.

SQL> select * from TT1;

A N
------------------------------------------------------------------------------------------
Aa 1
Bb 2
CC 3

 

Or

SQL> Create Table TT1 (A varchar2 (10), N number );

The table has been created.

SQL> insert into TT1 values ('A', 1 );

One row has been created.

SQL> insert into TT1 values ('bb', 2 );

One row has been created.

SQL> insert into TT1 values ('cc', 3 );

One row has been created.

SQL> commit;

Submitted.

SQL> select * from TT1;

A N
--------------------
Aa 1
Bb 2
CC 3

SQL> ALTER TABLE TT1 add (N1 clob );

Table changed

SQL> set linesize 200
SQL>/

A n N1
------------------------------------------------------------------------------------
Aa 1
Bb 2
CC 3

SQL> Update TT1 set n1 = N;

3 rows have been updated.

SQL> commit;

Submitted.

SQL> select * from TT1;

A n N1
------------------------------------------------------------------------------------
Aa 1 1
Bb 2 2
CC 3 3

SQL> ALTER TABLE TT1 drop column N;

The table has been changed.

SQL> select * from TT1;

A N1
-------------------------------------------------------------------------------------
Aa 1
Bb 2
CC 3

SQL> ALTER TABLE TT1 rename column N1 to N;

The table has been changed.

SQL> select * from TT1;

A N
-------------------------------------------------------------------------------------
Aa 1
Bb 2
CC 3

 

 

 

 

 

You can use the online dash definition: SQL> Create Table T1 (A varchar2 (10), N number );

SQL> insert into T1 values ('A', 1 );

1 row created.

SQL> insert into T1 values ('bb', 2 );

1 row created.

SQL> insert into T1 values ('cc', 3 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T1;

A N
------------------------------------------------------------------------------------------
Aa 1
Bb 2
CC 3

SQL> Create Table TT1 (A clob, N number); table created.

SQL> Create Table TT1 (A clob, N number );

The table has been created.

SQL> exec dbms_redefinition.can_redef_table ('Scott ', 'T1', 2 );

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.start_redef_table ('Scott ', 't1', 'tt1', 'to _ clob (a) A, n', 2 );

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.sync_interim_table ('Scott ', 't1', 'tt1 ');

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.finish_redef_table ('Scott ', 't1', 'tt1 ');

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.abort_redef_table ('Scott ', 't1', 'tt1 ');

The PL/SQL process is successfully completed.

SQL> DESC T1
Error:
ORA-04020: deadlock detected when trying to lock the object Scott. T1

SQL> commit;

Submitted.

SQL> DESC T1
Is the name empty? Type
Certificate -------------------------------------------------------------------------------------------------------------------------------

A clob
N number

SQL> dec TT1

SQL> DESC TT1
Is the name empty? Type
Certificate -------------------------------------------------------------------------------------------------------------------------------

A varchar2 (10)
N number

SQL> select * from T1
2;

A N
------------------------------------------------------------------------------------------
Aa 1
Bb 2
CC 3

SQL> select * from TT1;

A N
--------------------
Aa 1
Bb 2
CC 3

 

 

SQL> Create Table T1 (A varchar2 (10), N number );

The table has been created.

SQL> insert into T1 values ('A', 1 );

One row has been created.

SQL> insert into T1 values ('bb', 2 );

One row has been created.

SQL> insert into T1 values ('cc', 3 );

One row has been created.

SQL> commit;

Submitted.

SQL> Create Table TT1 (A clob, N number );

The table has been created.

SQL> ALTER TABLE T1 add primary key (N );

The table has been changed.

SQL> ALTER TABLE TT1 add primary key (N );

The table has been changed.

SQL> exec dbms_redefinition.can_redef_table ('Scott ', 'T1', dbms_redefinition.cons_use_pk );

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.start_redef_table ('Scott ', 't1', 'tt1', 'to _ clob (a) A, n ');

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.sync_interim_table ('Scott ', 't1', 'tt1 ');

The PL/SQL process is successfully completed.

SQL> exec dbms_redefinition.finish_redef_table ('Scott ', 't1', 'tt1 ');

The PL/SQL process is successfully completed.

SQL> DESC T1
Is the name empty? Type
Certificate -------------------------------------------------------------------------------------------------------------------------------

A clob
N not null number

SQL> DESC TT1
Is the name empty? Type
Certificate -------------------------------------------------------------------------------------------------------------------------------

A varchar2 (10)
N not null number

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The lob type cannot be used as the primary key.
SQL> ALTER TABLE T1 add constraint PK primary key ();
Alter table T1 add constraint PK primary key ()
*
Error at line 1:
ORA-02329: column of datatype lob cannot be unique or a primary key

 

 

 

 

SQL> ALTER TABLE T1 add N number;

Table altered.

 

SQL> select * from T1;

A N
------------------------------------------------------------------------------------------
Aaaa
Bbbbbb
Cccccc
Ddddd

SQL> Update T1 set n = 1 where a = 'aaa ';
Update T1 set n = 1 where a = 'aaa'
*
Error at line 1:
ORA-00932: inconsistent datatypes: expected-got clob.

 

SQL> Update T1 set n = 1 where a = to_lob ('aaa ');
Update T1 set n = 1 where a = to_lob ('aaa ')
*
Error at line 1:
ORA-00932: inconsistent datatypes: expected-got char

 

SQL> insert into T1 values ('eeeee ', 5 );

1 row created.

SQL> commit;

Commit complete.

You can insert the clob data type directly in the string format,

 

 

Clob does not support distinct queries
SQL> select distinct A from T1;
Select distinct A from T1
*
Error at line 1:
ORA-00932: inconsistent datatypes: expected-got clob.

Http://book.51cto.com/art/201202/319249.htm

Data in the table cannot be converted from varchar2 to long.