Perfect
Michael, July 23, 2001 - 8:42 pm UTC
Thanks Tom, the answer is perfect!
A reader, July 24, 2001 - 4:20 am UTC
Helpful, but why can't PL/SQL do this stuff?
Tired, October 24, 2001 - 4:11 pm UTC
This answer is helpful in that I want to unload some LONG RAWs into data files, so that I can look at them. Thus far, it's a brick wall at every turn. The OCI C program is helpful, but why oh why can't Oracle provide a REAL UTL_FILE package (something that will read/write files of arbitrary size) and REAL support for LONG RAW in PL/SQL? I write PL/SQL in my sleep. C takes more effort! (Plus I have to locate a compiler, get my environment set up, install OCI, etc.)
Helpful
Helen, November 28, 2001 - 7:20 am UTC
This points me in the right direction, I think, for converting a bunch of LONG datatypes that should really have been VARCHAR2's - thanks Tom
Exactly what I needed
Tom Best, September 04, 2002 - 10:05 am UTC
After developing a Java solution to do this in a test DB, I then went to production and realized I could not use the TO_LOB sql function (8.0.5). Then I went searching asktom some more, and found this OCI program that does EXACTLY what I needed to do.
Thanks, Tom.
BTW - you could have done this in Java right?... as shown in:
</code>
http://download-east.oracle.com/docs/cd/A87860_01/doc/java.817/a83724/basic4.htm#1010769 <code>
under "Getting a LONG RAW Data Column with getBinaryStream()". I admit I didn't try it though.
September 04, 2002 - 2:46 pm UTC
update in oci returning clause
umesh, July 18, 2003 - 4:34 am UTC
Tom
CREATE TABLE B_TEST (
ID NUMBER,
V VARCHAR2(20),
C CLOB,
B BLOB)
insert into b_test (id,v) values (1,'Hello');
now one of the developer updates the c,b column with following using oci calls
update b_test set c=empty_clob(),b=empty_blob()
returning c,b into :1,:2;
the above update is fine..
but if he says
update b_test set c=empty_clob(),b=empty_blob(), id=100
returning c,b into :1,:2;
this gives a error , he has put the blob columns,.. not at the end.. but some where in between..
If he tries to the same statement this way
update b_test set id=100, c=empty_clob(),b=empty_blob()
returning c,b into :1,:2;
it works fine with that oci call
is it mandatory that lob columns to be updated should be at the end??
July 18, 2003 - 8:45 am UTC
gee, "an error".
no, it should not be mandatory -- please file a tar with support (they'll be curious as to the "error"
CLOB to LONG??
Anirudh, February 10, 2004 - 5:41 am UTC
Hi Tom,
I am stcuk up with a requirement wherein I need to populate a LONG column at remote site with the data in our CLOB column. 8i doesn't support conversion of LOBs to LONGs, I guess.
I am getting a datatype mismatch when doing an INSERT...SELECT..
do I need to create a function which would return me LONG and in that function I would be taking my CLOB data and putting it to a LONG variable piece by piece...
Please share your viewpoint on this.
Anirudh
February 10, 2004 - 6:41 am UTC
nothing will support lobs to longs. longs to lobs -- sure. never the other direction.
if the clob is ALWAYS LESS THAN 32k in size, you can use plsql..
if the clob exceeds 32k -- you will HAVE to write a 3gl program in C, Java, whatever to do this.
conversion datatype
ganesh, April 21, 2004 - 3:27 am UTC
Hi tom,
this is very much useful. same thing I tried. As
we are oracle 9i. one table is having column of long raw and having data. now we need to chage datatype to blob.I used the same logic of
created new table of blob and inserted the reocord using to_lob. but data is not able to see from the application. it is currupting.
Even I used alter table table_name modify (col blob);
still facing the same problem.
Then we look into documentation for oracle 9i. there is one option in alter cluase
ALTER TABLE employees MODIFY (resume BLOB) DISABLE STORAGE IN ROW;
what is this meaning. Even if execute the above giving error like
ERROR at line 1:
ORA-00905: missing keyword
please suggest me. thanks
by Ganesh
April 21, 2004 - 8:00 pm UTC
umm, to_lobing the long raw to a blob will not "corrupt" the data.
application must not know how to process a blob. problem is in application.
disableing stored in row would have nothing to do with this whatsoever.
conversion datatype
Ganesh, April 22, 2004 - 6:31 am UTC
Hi tom,
thanks ! yes it was application problem.
The problem is like this. They have two tables.
one is temp table another is main table.
Temp table having column with BLOB data type and main table
having column with long raw data type.
First they inserting data into temp table and then inserting row into main table with direct insert and select.
My question is now they inserting blob to long raw.
If we do like this then is there any problem to currupt data. I think we can not convert blob to long raw.
only long raw to blob we can convert.
Am I right or wrong.
Not able veiw the doc files which are there in main table.
thanks in adv.
--Ganesh
April 22, 2004 - 8:11 am UTC
you can only go long raw -> blob, not the other way.
Trying to archive table data in 9i
Sandeep, January 24, 2005 - 8:36 am UTC
Hi Tom,
Sorry to bunge my query in this.. Hope this catches your attention!
I need to archive some application tables at a certain
frequency.
I plan to use a PL/SQL package which will do a select
from the source tables (based on some date criteria)
and insert these records into the appropriate destination (archive)table. When the insert is successful, the records
from the source would be deleted (1 transaction).
The problem I'm having is that some of the source tables
have columns which are LONG/ LONG RAW and CLOB's.
For the LONG and LONG RAW's my "insert into select * from"
is not working...
Considering that this is 9i - any suggestions from your side on how best I could do this archival?
Thanks,
Sandeep
January 24, 2005 - 8:47 am UTC
convert them to lobs... (in the archives at the very least)
you can insert into archive select to_lob( long_or_long_raw_column ) .....
else you are doing this row by row outside of the database. The long/long raws won't go any other way.
Thanks!
Sandeep, January 24, 2005 - 10:36 am UTC
Hi Tom,
Thanks for your quick reply.
I'll try the route of converting only the destination table
columns into LOB's.. as converting the source ones w/o
a major revolt and bloodshed might be impossible ;-).
Is it correct to say that the columns should be converted
like this?
- LONG --> CLOB
- LONG RAW --> BLOB
Thanks,
Sandeep
January 24, 2005 - 11:29 am UTC
correct.
Conversion Datatype
Vineet, February 17, 2005 - 3:20 am UTC
- Responses in this site are very good & helpful
The conversion function is the most helpful, it really reduced a lot of work on our side, as we had the deployed application with so much of actual data residing in the VARCHAR2 column of HTMLDB table, converted that CLOB, it works fine now.
Thanks,
Vineet
A reader, November 08, 2005 - 9:12 am UTC
Kevin, August 25, 2006 - 3:56 pm UTC
Hi,
I've a table name "current_image" that has 5000 images record in there. The "current_image" table structure is look like:
SQL> desc current_image
Name Null? Type
---------------------- -------- -----
IMAGE_ID NOT NULL NUMBER
FILE_NAME NOT NULL VARCHAR2(50)
IMAGE BINARY FILE LOB
SQL>
and now i would like to insert 5000 images in "current_image" table to a new table that has image column is clob datatype. Here is the step i did:
create table new_image as select * from current_image;
alter table new_image modify (image blob);
Here, how can i insert 5000 images records into "new_image" table?
Thanks,
August 27, 2006 - 8:50 pm UTC
... that has image column is clob datatype. ...
... alter table new_image modify (image blob); ...
confusion on my part.
you will likely use dbms_lob.loadfromfile - (funny you stored FILE_NAME when the bfile had the filename already?) after using the dbms_lob function to get the directory and filename from the original bfile column in current image
that is, you'll read the rows in current image
you'll use dbms_lob to get the directory and filename for the bfile for that
row
you'll select for update the "new_image" row that corresponds to that
and dbms_lob.loadfromfile the bfile
VARCHAR2 to CLOB
A Reader, September 25, 2006 - 10:20 am UTC
Hi Tom
I believe with Oracle 9i, it is possible to implicitly convert LONG columns to CLOB. What if I have to convert VARCHAR2 column to CLOB? Consider the example below.
SQL> create table testing
2 ( val VARCHAR2(2000));
Table created.
SQL> insert into testing values(rpad('*',2000,'*'));
1 row created.
SQL> commit;
Commit complete.
I need to convert VAL column in the above table to CLOB and migrate the existing data. Please advise.
Thanks
September 25, 2006 - 4:29 pm UTC
"implicitly convert" - no, there is no such thing.
explicitly, using TO_CLOB, yes, but that predates 9i actually.
Now, the question will be
a) can you take "offline" time to do this
b) do you need to do this online
c) what is YOUR version
Varchar2 to CLOB
A Reader, September 26, 2006 - 4:42 am UTC
Hi Tom
Thanks for the feedback. I am using Oracle 9i release 9.2.0.4. Can you please advise how to do this both online and offline. Your help is greatly appreciated.
Many Thanks
September 26, 2006 - 3:37 pm UTC
in 9i, it'll be offline and you'll just user "insert as select"
ops$tkyte%ORA10GR2> create table t1 as select 'hello' x from dual;
Table created.
ops$tkyte%ORA10GR2> create table t2 ( x clob );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t2 select * from t1;
1 row created.
Varchar2 to CLOB
A Reader, September 26, 2006 - 7:28 am UTC
Hi Tom
Thanks for the feedback. I am using Oracle 9i release 9.2.0.4. Can you please advise how to do this both online and offline. Your help is greatly appreciated.
Many Thanks
Bad link
Javier, October 11, 2006 - 7:38 am UTC
Hello,
I need to extract a long raw field in a oracle 9i database, and I tried to use the unload program of this link:
</code>
http://asktom.oracle.com/~tkyte/unload.tar.gz <code>
But the link is bad and I obtein a 404 error the requested URL was not found on this server.
Thank you in advance for your help
October 11, 2006 - 8:26 am UTC
restored, it is *really old* stuff.
BLOB to CLOB
Su Baba, November 08, 2006 - 3:16 pm UTC
What's the correct way to convert a BLOB column to a CLOB columns, assuming that everything we stored in the BLOB column is just text.
November 08, 2006 - 8:10 pm UTC
BLOB to CLOB Conversion...
Su Baba, November 14, 2006 - 3:41 pm UTC
So if I have a table that has a BLOB column that I want to convert to CLOB, I would
- Use DBMS_LOB.CONVERTTOCLOB to convert the BLOB to CLOB
record by record.
- Store this CLOB in a temporary table.
- When I am done with the entire table, I would "null out"
the BLOB column, convert the column to CLOB, and
update the column with the CLOB in the temporary table.
Would this be the right approach?
November 15, 2006 - 6:48 am UTC
or just add the clob column, copy the data over in the same table (no temporary stuff) and then drop the blob column.
A reader, December 06, 2006 - 4:57 am UTC
the functions doesnt work on 9i
December 07, 2006 - 8:20 am UTC
umm, what function exactly, your ability to be precise is not so good?
Su Baba, December 26, 2006 - 7:08 pm UTC
Referring to the "November 14, 2006" posting regarding "BLOB to CLOB Conversion", you mentioned
or just add the clob column, copy the data over in the same table (no temporary stuff) and then
drop the blob column.Is there any way to do a rough check on whether the BLOB column has been properly converted to CLOBs before I drop the BLOB column?
Will
SELECT SUM(dbms_lob.getlength(<BLOB column>)),
SUM(dbms_lob.getlength(<CLOB column>))
FROM <the table>
WHERE <BLOB column> IS NOT NULL;
and compare the value of the two columns work? If the CLOB column is encoded, will this still be true?
December 26, 2006 - 9:32 pm UTC
only you can say if the data is "correct", not really sure what you would be looking for there.
You have blob data, you forced it into a clob - if you used utl_raw to cast it, it is "there" but it might not be RIGHT given your characterset, only you know the answer to that.
Su Baba, December 27, 2006 - 6:19 pm UTC
Ok, if the output of the above SQL matches, it doesn't necessary tell us anything about whether the data have been converted properly. However, if the output doesn't match, can I assume there's something wrong with the conversion? Would that be a fair assumption assuming that the conversion is done using DBMS_LOB.CONVERTTOCLOB?
December 28, 2006 - 9:42 am UTC
i am not following you here - what would be a fair assumption and whatn "output doesn't match"?
Su Baba, December 28, 2006 - 12:34 pm UTC
Sorry for not being clear. I was referring to the SQL
SELECT SUM(dbms_lob.getlength(<BLOB column>)) blob_checksum,
SUM(dbms_lob.getlength(<CLOB column>)) clob_checksum
FROM <the table>
WHERE <BLOB column> IS NOT NULL;
<BLOB Column> is the original column. <CLOB Column> is the data converted from <BLOB Column> using DBMS_LOB.CONVERTTOCLOB.
If blob_checksum and clob_checksum do not match, can I assume that there's probably something wrong with the conversion or is this pretty much a useless check?
December 29, 2006 - 9:12 am UTC
so what if they are the same length? that does not mean the clob data is valid. the clob would have the bytes the blob had - without conversion. They may or may not be valid for your characterset.
and if you use converttoclob with characterset translation, then the lengths might be different on purpose.
moving table having blob from one db to other
amit, January 12, 2007 - 8:19 am UTC
Hi tom,
I want to know that can it be possible to make insert script for the table having blob column.I have use blob to refer my image file. now want to use same data but in different schema.
pls. guide
Long Raw to Blob Conversion
Amit Kumar Shai, January 19, 2007 - 2:27 am UTC
Hi,
I have written the following query but getting an error.
the error is : ORA-00932: inconsistent datatypes: expected - got BINARY
the query i have written is :
Select to_lob(signature)
From tm_signature
Where acc_type_cd = 1
And acc_num = '00665';
How can i solve this error ?
DBMS_LOB.CONVERTTOCLOB
Su Baba, February 28, 2007 - 7:29 pm UTC
When using DBMS_LOB.CONVERTTOCLOB to convert a BLOB into a CLOB, if the source BLOB is a text that is encoded in, say Latin-1, and we want the the CLOB to be UTF-8, what do we need to do to make sure the BLOB to CLOB conversion will not lose any data? Is there a conversion that needs to be done before calling DBMS_LOB.CONVERTTOCLOB?
February 28, 2007 - 10:07 pm UTC
no. this is what convert to clob does...
you need to make sure you have a utf-8 database...
Su Baba, March 01, 2007 - 11:57 am UTC
We do have a UTF-8 database. The BLOB was created by our application which has everything in Latin-1. The application load the BLOB into a UTF-8 database. Now we're trying to convert the BLOB into a CLOB. It seems that all the BLOBs that had only English contents were converted successfully. However, BLOBs that have special characters (e.g. German umlaut) were not being converted to CLOB correctly. The special characters were lost. Is there anyway around this problem using PL/SQL? Thanks.
alter table modify to clob.
Peter Littlefield, April 07, 2008 - 11:04 am UTC
In your very first response you say that in Oracle 9i we can alter a table's column (from varchar?) to clob. I get an error in oracle 10 ?
SQL> create table z(v varchar2(32));
Table created.
SQL> alter table z modify v clob;
alter table z modify v clob
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.3.0
My apolgies, I now see the from data_type is 'long raw', not varchar.
Peter Littlefield, April 07, 2008 - 11:06 am UTC
My apolgies, I now see the from data_type is 'long raw', not varchar.
April 09, 2008 - 9:02 am UTC
conversion to clob
Dmitriy Shestakov, June 17, 2008 - 3:06 pm UTC
June 17, 2008 - 3:30 pm UTC
before we go that route, what are you trying to do, what do you need to accomplish?
convert clob to blob
Alex, February 10, 2009 - 10:53 pm UTC
hi Tom
Please show me how to convert data from CLOB column to BLOB column in Oracle 9i.
thanks
February 11, 2009 - 10:32 am UTC
let me ask this first:
why?? because it doesn't make sense. I'd be interested in hearing the reasoning first.
convert clob to blob
Alex, February 11, 2009 - 11:05 pm UTC
I want to convert a clob to blob column but not lost data. this thing is possible in Oracle 10g. but in Oracle 9i I don't find the function to convert it.
Is this thing impossible in Oracle 9i?
thanks,
February 12, 2009 - 12:04 pm UTC
why do you want to do this...
please answer my question
for i have a feeling that when you do - we'll be able to say "that would be a really bad idea, let me tell you why"
I fully believe what you are trying to do would be a bad idea. You tell me why, and then I'll tell you how.
how about huge tables?
keshav, February 12, 2013 - 4:21 pm UTC
In my DB, the table with LONG column has 150 million records. In the process of converting this column to CLOB, we tried using datapump (expdp+impdp) that is accounting to 3hrs+16hrs.
I want to try to_lob function as
insert into xyz select to_lob(longcol)
Even if I add parallels in the insert and select, the above DML is taking 10+ hrs to complete and then I need huge undo/rollback to commit.
Are there any better options to convert long to clob for huge tables?
February 12, 2013 - 6:10 pm UTC
no mention of a version :( dbms_parallel_execute could be something to look at - but who knows if it applies to you.... 11g...
why not create table as select OR insert /*+ APPEND */ (direct path)
direct path can skip undo and optionally much of the redo generation (if in noarchivelog mode or if table is NOLOGGING and force_logging is false).
ops$tkyte%ORA11GR2> create table t
2 (
3 OWNER VARCHAR2(30),
4 VIEW_NAME VARCHAR2(30),
5 TEXT_LENGTH NUMBER,
6 TEXT clob,
7 TYPE_TEXT_LENGTH NUMBER,
8 TYPE_TEXT VARCHAR2(4000),
9 OID_TEXT_LENGTH NUMBER,
10 OID_TEXT VARCHAR2(4000),
11 VIEW_TYPE_OWNER VARCHAR2(30),
12 VIEW_TYPE VARCHAR2(30),
13 SUPERVIEW_NAME VARCHAR2(30),
14 EDITIONING_VIEW VARCHAR2(1),
15 READ_ONLY VARCHAR2(1)
16 )
17 nologging
18 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace on statistics
ops$tkyte%ORA11GR2> insert into t
2 select owner, view_name, text_length, to_lob(text),
3 type_text_length, type_text, oid_text_length,
4 oid_text, view_type_owner, view_type, superview_name,
5 editioning_view, read_only
6 from dba_views
7 /
5176 rows created.
Statistics
----------------------------------------------------------
1239 recursive calls
12974 db block gets
10521 consistent gets
439 physical reads
11681232 redo size
919 bytes sent via SQL*Net to client
1204 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
5176 rows processed
ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select used_ublk from v$transaction;
USED_UBLK
----------
64
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> truncate table t;
Table truncated.
ops$tkyte%ORA11GR2> set autotrace on statistics
ops$tkyte%ORA11GR2> insert /*+ append */ into t
2 select owner, view_name, text_length, to_lob(text),
3 type_text_length, type_text, oid_text_length,
4 oid_text, view_type_owner, view_type, superview_name,
5 editioning_view, read_only
6 from dba_views
7 /
5176 rows created.
Statistics
----------------------------------------------------------
368 recursive calls
3679 db block gets
8675 consistent gets
424 physical reads
6388740 redo size
903 bytes sent via SQL*Net to client
1218 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5176 rows processed
ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select used_ublk from v$transaction;
USED_UBLK
----------
4
problem after converting long raw to blob
Mohamed, July 21, 2013 - 7:59 am UTC
Hi tom,
I'm working on DB 10g R2 I have a table includes long raw column to save documents (pdf,doc,jpg,etc..)
and I migrated to a new table which have blob datatype using
create new_table as select id,to_lob(long raw column) from the old table and it was created successfully but the problem when I try to download the file to the hard disk it never open it seems it's corrupted pdf files doesn't open and word document shows corrupted format and everything
note: I used to insert these files using oracle forms 6i thru ole item
when I try to use blob with the ole item it saves with 0 size in the blob column and does't show in the OLE object
i'm still using oracle forms 6i client server Application
please advise
thanks
July 31, 2013 - 3:40 pm UTC
write a program to read the long raw, write to file, read the blob, write to file and verify they are the same. that is step 1
step 2 which could be done concurrently with step 1 would be to get onto a forms forum and ask some questions - I haven't used forms since 1995 and OLE isn't anything I've ever worked with.
raw to varchar2 conversion
ajeet, November 05, 2013 - 10:50 am UTC
Hi Tom,
I have a column in a table with Data type as RAW.
it has typical value as
"0000000C0000000C0000000C3030303030303030011660258C40404040404040402020202020202020202020202020202020202020303030303030303020"
I am trying to convert it into varchar2 using utl_raw.cast_to_varchar2 .it return null for the above value.
I am sure I am doing something wrong, can you please let me know what is the correct way to do it.
November 07, 2013 - 3:19 pm UTC
it starts with 0x0
it is an empty string if you convert it to a string.
what do you expect it to contain? 0x0 is not a character.... what did you anticipate being in the string?
you cannot put a raw in general into a string, that is why we have strings in the first place!
raw to varchar2 conversion
Michel Cadot, November 18, 2013 - 10:31 am UTC
Ajeet,
Which version are you using?
Oracle fixed the 0x00 issue with cast_to_varchar2 since, at least, 10.2.0.3 (the oldest version I currently check):
SQL> create table t (col raw(62));
Table created.
SQL> insert into t values(hextoraw('0000000C0000000C0000000C3030303030303030011660258C40404040404040402020202020202020202020202020202020202020303030303030303020'));
1 row created.
SQL> commit;
Commit complete.
SQL> select utl_raw.cast_to_varchar2(col) from t;
UTL_RAW.CAST_TO_VARCHAR2(COL)
--------------------------------------------------------------------------------
♀ ♀ ♀00000000☺▬`%î@@@@@@@@ 00000000
1 row selected.
Regards
Michel