Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Akshay.

Asked: January 23, 2018 - 2:46 pm UTC

Last updated: January 28, 2018 - 5:21 am UTC

Version: oracle 11

Viewed 10K+ times! This question is

You Asked

I Have table, which has clob data type, value stored is in hexadecimal data type.
Please let me know how can i convert clob data to varchar

and Connor said...

There is no such thing as hexadecimal data type. If I had to guess, you might have raw input that got put into the clob ? If so, you could cast it back out again.

SQL> create table t ( x clob );

Table created.

SQL> insert into t values ( '434F4E4E4F52');

1 row created.

SQL> select utl_raw.cast_to_varchar2(cast(x as varchar2(100))) from t;

UTL_RAW.CAST_TO_VARCHAR2(CAST(XASVARCHAR2(100)))
-----------------------------------------------------------------------
CONNOR


Are you sure its not a blob ? We can't really do much without a test case

Rating

  (3 ratings)

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

Comments

Data size

A reader, January 25, 2018 - 3:28 am UTC

The data size of clob column is more then 20000.
When clob data column is open, text tab is blank.
Only hexadecimal tab has value in it.
How to retrieve it and convert it to varchar
Connor McDonald
January 27, 2018 - 2:16 am UTC

Give us the full DDL for the table concerned please.

clob conversion

A reader, January 26, 2018 - 11:27 am UTC

size of clob is more then 25,000.
how to stored it and covert inot varchar2.
I am using plsql developer tool, when clob row is open text tab is blank. data is availabel in hexadecimal tab only.
Please let me know how to convert it.

CLOB DATA CONVERSION

A reader, January 27, 2018 - 7:42 am UTC

CREATE TABLE C_TEST(ID INETGER, T_FILE BLOB);
Connor McDonald
January 28, 2018 - 5:21 am UTC

Can you see something obvious here ?

"I Have table, which has CLOB data type"

versus

"CREATE TABLE C_TEST(ID INETGER, T_FILE BLOB);"

Totally different things "CLOB" and "BLOB"....This is why we ask people give us test cases in AskTOM so we don't waste our time and yours.

A blob contains *binary* data - the only reason we show it to you as hex is so that the client software can give you a meaningful presentation of it.

That blob content could be anything - a word document, a picture, a video - if you want to download it, you can see here:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=retrieving-data-from-blob-into-a-file
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

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database