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;
/
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 ?
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.
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.
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.
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?
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,
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
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
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
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
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
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
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.
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
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 :)
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 . . .
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 . . .
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?
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??
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.