substitute the <CR><LF> with the string '<BR>' in Clob data co 1

[henryz]

Hi,

I have a table in Oracle 8i, with a clob column.

Now I want to execute a query like the following:

REPLACE(<clob_column>, CHR(13)||CHR(11), '<BR>')

But I receive an error complain with "Inconsistent datatype". Can anybody suggest a workaround for this please? We need this very urgent.

Thanks in advance,
Henry

 

[SantaMufasa]

Sorry, Henry, treating a CLOB column as you can a VARCHAR2 column didn't appear until Oracle 9i. You can create CLOB columns in pre-9i versions, but they behave like LONGs not VARCHAR. Everything works as you wanted with CLOBs in Oracle 9i:

select * from henry;

CLOB_COL
-------------------------------------
Prior to <cr><lf>
Following <cr><lf>

select replace(clob_col,CHR(13)||CHR(11), '<BR>')
from henry;

REPLACE(CLOB_COL,CHR(13)||CHR(11),'<BR>')
-----------------------------------------
Prior to <cr><lf><BR>Following <cr><lf>

If you cannot upgrade your database to Oracle 9i, then amongst your options are:

1) Obtain and install "Oracle Text" (previously known as both "ConText" and "Intermedia") for Oracle 8i. Then use that package's functionality to update your CLOB column.

2) Export your table containing the CLOB to an Oracle 9i database; UPDATE the CLOB column with the "<BR>" string, then export the table back to Oracle 8i.

If you do not want to pursue Option 1 and do not have the resources for Option 2, then I would be willing to perform the data modification for you and return an Oracle 8i import (dump) file to you. Contact me at "dave at dasages dot com" if that is of interest to you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

BTW, if this is rather urgent and you do want me to help you presently, please reply quickly since I was just heading to bed. (It's midnight here, but about 2 p.m. where you are I'll bet.)

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[henryz]

Thanks Mufasa for your kind response. I am based in Sydney, Australia.

Finally I manage to solve this problem by using this workaround:

replace((dbms_lob.substr(clob, dbms_lob.getlength(clob),1)), chr(13)||chr(10), '<BR>')

It does not look pretty, but it works.

Cheers,
Henry

 

[SantaMufasa]

Well done, Henry ! Your solution actually looks just fine considering how messy the alternatives could have been. Have a star for coming up with a solution that, I'm sure, will be very helpful to Tek-Tipsters in the future with similar needs.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[henryz]

Mufasa,

Thanks for your cheering comments.

Regards,
Henry