Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: July 23, 2001 - 11:00 am UTC

Last updated: November 07, 2013 - 3:19 pm UTC

Version: 8

Viewed 10K+ times! This question is

You Asked

Hi,

I would like to know if we can convert a long raw column into a clob or blob or bfile.

If it is possible, can you say how?

Thanks and regards.

and Tom said...

In Oracle8i release 8.1 and up, yes:

create table new_table as select c1, c2, TO_LOB(c3) from old_table;

or

insert into new_table select c1, c2, to_lob(c3) from old_table;


In Oracle9i, you can even:

alter table old_table modify ( c clob );

to convert it.

In 8.0, you must unload and reload manually. You can use plsql to convert a clob:

create or replace procedure long2clob( p_query in varchar2,
p_bindvar in varchar2,
p_clob in out clob )
as
l_cursor integer default dbms_sql.open_cursor;
l_long_val varchar2(20);
l_long_len number;
l_buflen number := 20;
l_curpos number := 0;
l_n number;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );

dbms_sql.bind_variable( l_cursor, ':bv', p_bindvar );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
exit when l_long_len = 0;
dbms_lob.write( p_clob, l_long_len, l_curpos+1, l_long_val );
l_curpos := l_curpos + l_long_len;
end loop;
end if;
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end long2clob;
/


drop table test;
create table test ( y clob );

declare
l_clob clob;
begin
for x in ( select view_name from user_views where rownum < 25 )
loop
insert into test values ( empty_clob() ) returning y into l_clob;
long2clob( 'select text from user_views where view_name = :bv',
x.view_name,
l_clob );
end loop;
commit;
end;
/
select dbms_lob.getlength(y) from test
/

but you must use C or some other 3gl for LONG RAW as there is no PLSQL interface for them at all.

If you are interested, I have a very small OCI program that will unload any LONG RAW from a table into a file (each row will become a separate file -- you select out two columns, the filename and the long raw to put into the file). You can then use DBMS_LOB.LOADFROMFILE to load the files back into the BLOB columns (or use the BFILE type on the files you just created)

</code> http://asktom.oracle.com/~tkyte/unload.tar.gz <code>

there are makefiles for unix and nt, you need a c compiler and oci. The usage is simply:

useage: ./unload user/pass@db 'select LONG_RAW, FILE_NAME from t where ...'

for every row that query returns -- it'll create a file named FILE_NAME and put the LONG_RAW column into it.






Rating

  (37 ratings)

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

Comments

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.

Tom Kyte
September 04, 2002 - 2:46 pm UTC

Yes... it could look like this:

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

even


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??



Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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,

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
November 08, 2006 - 8:10 pm UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#sthref3647 <code>



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?

Tom Kyte
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

Tom Kyte
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?
Tom Kyte
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?
Tom Kyte
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?
Tom Kyte
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?
Tom Kyte
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.
Tom Kyte
April 09, 2008 - 9:02 am UTC

right, it only works for long and long raws

http://www.oracle-base.com/articles/9i/LOBEnhancements9i.php

conversion to clob

Dmitriy Shestakov, June 17, 2008 - 3:06 pm UTC

Tom,
I'm a novice in a C programming. I downloaded this http://asktom.oracle.com/~tkyte/unload.tar.gz

I have a c compiler installed. I believe I have a OCI. How do I compile program ? make makefile.unx says it is up to date
Tom Kyte
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
Tom Kyte
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,
Tom Kyte
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?
Tom Kyte
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
Tom Kyte
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.
Tom Kyte
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

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here