Skip to Main Content
  • Questions
  • How to convert a table with long_row data into a CLOB data type table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Sue.

Asked: August 29, 2001 - 4:09 pm UTC

Last updated: May 08, 2007 - 10:41 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Developer was asking a question about how to convert a table with
long_row data into a CLOB data type table? Does Oracle provide
any utility to do it?

and Tom said...



Create table new_table
as
select c1, c2, c3, to_lob(c4) c4
from old_table
/


In Oracle9i -- there is a very cool "alter table t modify long_col CLOB" to do this as well....



Rating

  (24 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

long raw to blob..... same kind of question.

anshula, August 30, 2001 - 7:44 am UTC

columns in test_long_raw_tab are id (number) and text_long_raw (long raw)
columns in test_blob_tab are id( number) and text_blob (blob).

Can I use this to insert data from test_long_raw to test_blob
if test_long_raw_tab contains image? it working file for text files.

declare
cursor c1 is
select text_long_raw
from test_long_raw_tab
where id = 200000020;
begin
for c In c1 loop
insert into test_blob_tab values (1,c.text_long_raw);
commit;
end loop;
end;
/

Tom Kyte
August 30, 2001 - 8:01 am UTC

It only works for LONGS and LONG RAWS that are 32k and less.

An INFINITELY more efficient method would be simply:


insert into test_blob_tab
select 1, to_lob(text_long_raw)
from text_long_raw_table
where id = .......;

just do it in a single statement.

A reader, September 04, 2001 - 9:54 am UTC

Thanks for answering my question.
Using create table X as select ... is only for same tablespace; but our CLOB are created in defferent tablespace. How do we solve this problem ?

Tom Kyte
September 07, 2001 - 12:16 pm UTC

Perhaps the easiest method is to create the table first and then use INSERT INTO SELECT ... to_lob() instead of CREATE TABLE as SELECT ....


You can do it in the CTAS however:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 ( x, y )
  2  tablespace users
  3  lob (y) store as ( tablespace drsys disable storage in row )
  4  as
  5  select x, to_lob(y)
  6  from t;

Table created.

 

long to clob conversion in 9.2

Baqir Hussain, February 10, 2003 - 2:22 pm UTC

Tom,
It's really cool to change long into clob using the following sql query
alter table journal modify body clob;
The table has over 4 million records and would take more than 2 hours to convert from long to clob.

Is there any other way to expediate this procedure?
thanks and oblige.

Tom Kyte
February 11, 2003 - 7:43 am UTC

CTAS with parallel nologging followed by
reindex (which you have to do after an alter table modify as well)
grant
constrain
rename of table.

update a long

Kumar, October 04, 2003 - 1:19 pm UTC

Tom, I have an urgent requirement at work to update a long column that contains a two page document for every row in a table. I have been asked to update two sentences in the long col doc in such a way that If I find these two sentences in each long col, update them to different information. Please help me how i can do it? Thanks much.

Tom Kyte
October 04, 2003 - 1:57 pm UTC

what is the size of the longest long?

and what is your favorite programming language in case you say "a number bigger then 32k"

update a long col

Kumar, October 04, 2003 - 7:02 pm UTC

what is the size of the longest long?

It is about 20k. The database version is 8.1.7.3.
Thanks.

Tom Kyte
October 04, 2003 - 7:24 pm UTC

declare
temp_col long;
begin
for x in ( select rowid rid, t.* from t )
loop
if ( x.long_column like '%strings you are interested in%' )
then
temp_col := ..... whatever you want to fix string up to be, use
substr/instr, whatever to fix it up....
update t set long_column = temp_col where rowid = x.rid;
end if;
end loop;
end;
/

Thanks so much.

Kumar, October 04, 2003 - 11:56 pm UTC


Great information, but need to know...

Mike, January 29, 2004 - 8:46 pm UTC

How much space is needed to run:

alter table t modify long_col CLOB?

I think I figured out my space question

Mike, January 30, 2004 - 2:29 pm UTC

To convert a long to a clob in 9i you need at least as much space as the table currently uses. It looks like Oracle rewrites the entire table, right?

Tom Kyte
January 30, 2004 - 7:57 pm UTC

yes, it is basically rewriting it.

progress of modify

Gabriel, July 20, 2004 - 5:16 pm UTC

Hello Tom,

Is there a way to check the progress of the modify command during its execution? like number of rows converted up to a certain point (now) before the completion of the command?

Thank you,



Tom Kyte
July 20, 2004 - 8:56 pm UTC

You can monitor the progress using v$session_longops

select * from v$session_longops where time_remaining > 0

it'll show you:

Table Scan:  OPS$TKYTE.FOO: 10913 out of 21249
...
Table Scan:  OPS$TKYTE.FOO: 14113 out of 21249


how many blocks its gotten thru.


and even try to estimate completion time:

TIME_REMAINING                : 6
ELAPSED_SECONDS               : 83
CONTEXT                       : 0
MESSAGE                       : Table Scan:  OPS$TKYTE.FOO: 19794 out of 21249
 

Convertion from LONG RAW to CLOB

Praveen, January 25, 2005 - 4:41 am UTC

Hi Tom,

I tried the below given example but the last statement is throwing an exception
"ORA-00932: inconsistent datatypes: expected CLOB got BLOB"

create table LONG_TBL (id integer, longcol long raw);

insert into LONG_TBL values (1, rpad('A', 100000, 'A'));
insert into LONG_TBL values (1, rpad('B', 2000000, 'B'));
insert into LONG_TBL values (1, rpad('C', 30000000, 'C'));

commit

create table CLOB_TBL (id integer, clobcol clob)

insert into CLOB_TBL (id, clobcol)
select id, to_lob(longcol) from LONG_TBL;

The example given in your answer to the question in this thread is doing the same. Ofcourse, to_lob() successfully converts LONG RAW to BLOB. I tried to_clob() function also but got the error "ORA-00932: inconsistent datatypes: expected NUMBER got BINARY"

Tom,

1) is it possible to convert a long_raw column value into a clob column value through an sql? If not is it possible using a procedure?

2) How about conversion from CLOB to LONG RAW/BLOB?

Regards

Praveen

Tom Kyte
January 25, 2005 - 9:19 am UTC

1) no, LONG -> CLOB. LONG RAW -> BLOB

2) you could convert a clob to a blob, or a blob to a clob -- using utl_raw.cast_to_varchar2/raw and doing it 32k at a time.

converting blob to clob

Praven, January 26, 2005 - 12:08 am UTC


Tom Kyte
January 26, 2005 - 8:42 am UTC

let me also mention the dbms_lob.convert routine too. depending on your release, you may or may not have this newish feature:

</code> http://docs.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_lob.htm#1017117 <code>


How Size of LONG RAW

Ignatius, March 02, 2005 - 6:57 am UTC

Dear Sir,

How do i find the size of LONG RAW, whether its 32K or something other.

Thank you very much

Tom Kyte
March 02, 2005 - 7:40 am UTC

in plsql, you can only do this if the long raw is less than 32k -- anything over that, plsql cannot fetch it.

in other languages, you have to fetch it all and see how much you get.

if you use blob, (you can to_lob a long raw), you can find this easily.

ORA-01406: fetched column value was truncated

Ignatius, March 02, 2005 - 8:01 am UTC

Dear Sir,

Thank you very much for your useful answer.

I have a requirement wherein i have to update the LONG RAW columns of the table

my PL/SQL gives me an error of "ORA-01406: fetched column value was truncated"

can you please help

following is my PL/SQL

Declare
cursor C1 is
select column1, column2, column_long_raw
from table_A@db_link_name;

temp_col_long_raw long raw;
v_excep number := 0;
v_counter number := 0;
begin
for c2 in C1 loop

Begin
select column_long_raw into temp_col_long_raw
from table_A
where column1 = c2.column1
and column2 = c2.column2;
Exception when others then
v_excep := v_excep + 1;
end;

if temp_col_long_raw <> C2.column_long_raw then
v_counter := v_counter + 1;
end if;

end loop;

dbms_output.put_line('count ' ||v_counter);
dbms_output.put_line('excep ' ||v_excep);

commit;
end;
/

declare
*
ERROR at line 1:
ORA-01406: fetched column value was truncated

Thank you very much.
Ignatius


Tom Kyte
March 02, 2005 - 8:10 am UTC

and is it bigger than 32k..............

How do i find it if its bigger than 32K

A reader, March 02, 2005 - 10:42 pm UTC

Dear Sir,

How do I find it, if its bigger than 32K or not. I need your guidance and help.

Thank you very much

Tom Kyte
March 03, 2005 - 7:18 am UTC

given that it was truncated.....

but you could:

create table t as select to_lob(long_raw_column) data from t2 <where ...>
select dbms_lob.getlength(data) from t;



how much space can blob use

A reader, April 05, 2006 - 7:52 am UTC

Hi

I would like to know if I load a 100mb file to a blob field will it consume 100mb in the database? More?

Cheers

Tom Kyte
April 05, 2006 - 5:57 pm UTC

it'll likely take more than 100mb to store since there is

a) a lob locator in the table that points to
b) a lob index that has many entries that point to
c) the lob segment which stores the 100mb in chunks with associated overhead on each chunk.

10g Clob

Yogesh, May 10, 2006 - 10:23 am UTC

What should be the correct answer for followng question?

Character large objects (CLOB) data is represented in Oracle database 10g as the ___- character set.

A. UCS2
B. US7ASCII
C. AL16UTF16
D. WE9IS08859P1
E. D7SIEMENS9780X
F. Same characters set as the database character set.


Tom Kyte
May 10, 2006 - 10:41 am UTC

why are you giving me test questions? (this being the second quiz question you've given me in the last minute).


The answer is "none of the above", none of them answer the question correctly.

but based on the documentation, one would be tempted to pick f.
</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_intro.htm#sthref47 <code>

when using multi-byte data, we don't necessarily use the database characterset though.

tell the quiz masters to read Note 257772.1 on metalink.

the answer is:

A, C and F under different circumstances :)

CLOB format

Michel Cadot, May 10, 2006 - 10:57 am UTC

Yogesh,

It's irritating to have the same question in two threads. It becomes hard to follow the issue.
Can you please post your question in only one place.

Tom,

Can you choose one of the two threads to follow-up this interesting question on CLOB issue when migrating to 10g and point to the one you choose in the another one.

For those who are interesting in, the other thread is at:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7145231318014#63681623276317 <code>

Thanks
Michel

Converting Varchar2 data into Long raw

Ashish, May 25, 2006 - 3:27 am UTC

Hi Tom,

Is it possible to convert Varchar2 data into Long raw ?

Thanks,
Ashish

Tom Kyte
May 25, 2006 - 1:43 pm UTC

utl_raw.cast_to_raw

Opposite function for UTL_RAW.CAST_TO_RAW on 10gr2

Juancarlosreyesp@yahoo.com, June 20, 2006 - 12:39 pm UTC

Hi Tom could you please helpme

To find text on a oracle forms stored on a blob we use the following function

DBMS_LOB.INSTR(blob_column,UTL_RAW.CAST_TO_RAW(
'texttofind', 1, 1) <> 0

But I need to change to lowercase before find that text
I was asking me how can I get the opposite effect than UTL_RAW.CAST_TO_RAW.

The blob_column is bigger than 1Mg
I was looking for something like
INSTR(lower(UTL_RAW.raw_to_CAST(blob_column)),
'texttofind', 1) <> 0

But up to the moment I can't find nothing similar. I tried several things, like a function blob_to_clob, to get a value and insert in a temporal table, but this is not working. So I decided to ask you.

Thank you Tom :)

Tom Kyte
June 21, 2006 - 9:32 am UTC

multi-byte charactersets will be the end of you... But - you could write your own plsql function that

offset = 0
loops over the length(blob)/31k + 1
get substr of blob from offset for 32k
use utl_raw.cast_to_varchar2 on that 32k chunk
use like on that
advance offset by 31k
end loop


that is, you walk the blob in 32k pieces (advancing only 31k so you have overlap in case texttofind spans a 32k boundary). Your function would return found or not found..

A reader, June 22, 2006 - 8:53 am UTC

Thanks Tom, there was a failure on the tool used to see clobs,
DBMS_LOB.CONVERTTOCLOB(v_clob,v_blob,
v_amount,v_clob_offset,
v_blob_offset, 1,
v_lang_context,v_warning);
Works great.
Thank you.

Queries on LONG columns

Mike, October 10, 2006 - 6:08 am UTC

I am trying to extract the names of check constraints on a table, so that I can use them to call DBMS_METADATA to generate suitable statements for them. But I must not include NOT NULL constraints, as these will cause DBMS_METADATA to throw an error.

Reviewing the catalog view SYS.DBA_CONSTRAINTS, it looks simple: if SEARCH_CONDITION is LIKE '%IS NOT NULL' then I should exclude it. But my efforts have been thwarted by the inconvenient LONG datatype.

Most of my attempts look something like this:
SELECT * FROM SYS.DBA_CONSTRAINTS
WHERE OWNER = &OWNER
AND TABLE_NAME = &TABLE_NAME
AND CONSTRAINT_TYPE = 'C'
AND TO_LOB(SEARCH_CONDITION) NOT LIKE '%IS NOT NULL'

This particular query fails with ORA-00932 - inconsistent datatypes: expected - got LONG.

I have searched for various alternatives:
- TO_LOB only works during an INSERT
- CAST does not support LONG
- TO_CHAR does not support LONG
- etc.
Is there any way to manipulate LONG data like this, without resorting to a second table? (My immediate need is for Oracle 9.2.)

This is not the first time I have found Oracle's use of LONG datatypes in the catalog views to be very, very frustrating - DBA_VIEWS is another offender . . .

Tom Kyte
October 10, 2006 - 8:17 am UTC

one approach:

for x in (your select without the is not null bit)
loop
if ( x.search_condition not like '%IS NOT NULL' )
then
....


another approach, long_substr:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:665224430110 <code>

Queries on LONG columns

Mike, October 10, 2006 - 8:47 am UTC

Thanks very much - the LONGSUBSTR approach sounds promising and I will try that.

The exceptional turnaround time, with two specific solutions offered, is amazing. And I'm guessing that whatever timezone you are in would make this downright scary . . .

Tom Kyte
October 10, 2006 - 12:09 pm UTC

Mike - I'm in Ohio this morning, down in Dublin... :)

how to load data

C.Ashok Kumar, April 20, 2007 - 3:54 am UTC

Can u help me in knowing how to load data in to columns having datatype as long and clob?
Tom Kyte
April 20, 2007 - 7:09 am UTC

"U" is not around, but in the meantime, if you can find a copy of either of Expert one on one Oracle or Expert Oracle Database Architecture - big chapter on this topic.

question is bigger than you think - is the data in the file to be loaded along with the other stuff, how is it delimited, is it stored in a file and your input file has that file name, does it contain new lines...

Stored Procedures

C.Ashok Kumar, May 07, 2007 - 1:47 am UTC

Could you please tell me why we have only stored procedures??...I have never heard anyone telling stored functions or stored triggers or stored packages eventhough these things are also stored in database...is it just a naming or something more than that??
Tom Kyte
May 08, 2007 - 10:41 am UTC

convention, people - by convention - just call them stored procedures.

of course there are functions, packages, triggers, views, etc. there are lots of compiled stored objects.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here