Oracle long type to clob type

Insert into sammy_test_clob
Select to_lob (report_xml)
From qm_s_report
Where report_name = 'sammy ';

To_lobFunctions andLongThere are many restrictions on the same type. To put it simply,To_lobGenerallyCreate TableOrInsert tableSubquery after statement. If it is used elsewhere, an error is reported, for exampleUpdateStatement.

This is not the biggest problem. The biggest problem is,To_lobThe function does not seem to actuallyLongType conversionLobData type. Personal feeling,OracleOnlyLongType to storeLobType.

From: http://blog.csdn.net/gbnew/archive/2007/07/20/1700596.aspx

To_lobA function is a special function. It can handleLongType data, and this function andLongThe same type has many restrictions. However, these are not very special. Let's take a look at them.To_lobThis function.

 

OracleOfLongThe type is "Notorious" becauseLongThere are too many restrictions on the type, so thatOracleSeldom mentionLongWhich restrictions are imposed on the type, and generally the conditions under which the type can be usedLongType.

These restrictions preventLong,OracleA large object type is also introduced --LobWe strongly recommend that you do not useLongType.

However, the irony is that,OracleWe recommend that you stop usingLongType, but it can be seen everywhere in the data dictionaryLong. And, even if it is currently the highest version10r2,LongTypes are still everywhere in the data dictionary. Don't knowOracleIs it about compatibility or other reasons?OracleStill notLongThe data type is removed from the data dictionary. I don't know if there is any change in 11g.

AlthoughOracleHowever, we recommend that you do not useLong, And useBlob,ClobTo replaceLongField. AndLongThe Type restriction does also cause headaches.LongType conversionLobThe tool of the type isTo_lobFunction.

To_lobFunctions andLongThere are many restrictions on the same type. To put it simply,To_lobGenerallyCreate TableOrInsert tableThe subquery after the statement is in progress. If it is used elsewhere, an error is reported, for exampleUpdateStatement.

This is not the biggest problem. The biggest problem is,To_lobThe function does not seem to actuallyLongType conversionLobData type. Personal feeling,OracleOnlyLongType to storeLobType.

SQL> Create Table T1 (ID number, text clob );

The table has been created.

SQL> Create Table T2. (ID number, text varchar2 (4000 ));

The table has been created.

SQL> insert into T1 select rownum, text from dba_views;
Insert into T1 select rownum, text from dba_views
*The1Row Error:
ORA-00997: Illegal use of long datatype

SQL> insert into T1 select rownum, to_lob (text) from dba_views;

Created2268Line.

SQL> commit;

Submitted.

UseTo_lobYou can setLongInsert dataClobField, but if you wantLongInsert dataVarchar2Medium:

SQL> insert into T2 select rownum, text from dba_views;
Insert into T2 select rownum, text from dba_views
*The1Row Error:
ORA-00997: Illegal use of long datatype

SQL> insert into T2 select rownum, dbms_lob.substr (to_lob (text), 4000, 1) from dba_views;
Insert into T2 select rownum, dbms_lob.substr (to_lob (text), 4000, 1) from dba_views
*The1Row Error:
ORA-00932: inconsistent datatypes: expected-got long

You cannot insert data directly, but you have just obtainedClobTypeClobConvertVarchar2No? However, the results are indeed unexpected. Observe the error message,OracleThe returned data type isLong. It seemsTo_lobData type conversion is not performed. Verify the following:

SQL> select dump (to_lob (text) from dba_views;
Select dump (to_lob (text) from dba_views
*The1Row Error:
ORA-00932: inconsistent datatypes: expected-got long

SQL> select dump (text) from T1;
Select dump (text) from T1
*The1Row Error:
ORA-00932: inconsistent datatypes: expected-got clob.

We can see from this comparison that,To_lobThe function does not return as expected.ClobType, but the actually returned isLongType.

SQL> insert into T2 select rownum, to_lob (text) from dba_views;

Created2268Line.

Direct useTo_lobIt seems that the data can be inserted, but after careful comparison, we will find that,LongType data is not actually inserted into the table:

SQL> Col text format A50
SQL> set long 50
SQL> select * From T2 where rownum <3;

Id text
------------------------------------------------------------
1
2

SQL> select * from T1 where rownum <3;

Id text
------------------------------------------------------------
1 select owner, table_name, tablespace_name, cluster
2 select a. apply_name, A. queue_name, A. queue_owner,