Replacing Characters in Long Data Type

[TStriker]

Rookie Oracle guy here.

I want to replace a set of characters in an Oracle 10g long data type field with another set of characters.

In plain english, it's like changing the word DARKNESS to SUNSHINE everywhere DARNKESS appears.

This would be easy in a Varchar field but sadly for me it's in a LONG datatype field.

Any ideas?

-Striker

 

[SantaMufasa]

TStriker,

I'm curious...why, if you have Oracle 10g, didn't the database designer use a CLOB instead of a LONG? You can use CLOBs just like VARCHAR/VARCHAR2.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Santa,
I've been curious but have no Oracle to test on..Can you alter the table to change the type from LONG to CLOB like:

Alter table MyTable Modify Long_Column CLOB;






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Actually, yes, you can change a LONG to a CLOB:

SQL> create table long_to_clob (x long);

Table created.

SQL> insert into long_to_clob values ('hello');

1 row created.

SQL> insert into long_to_clob values ('This is a long value.');

1 row created.

SQL> select * from long_to_clob;

X
--------------------------------
hello
This is a long value.

SQL> desc long_to_clob;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----
 X                                                  LONG

SQL> alter table long_to_clob modify (x clob);

Table altered.

SQL> desc long_to_clob
 Name                                      Null?    Type
 ----------------------------------------- -------- -----
 X                                                  CLOB

SQL> update long_to_clob set x = replace(x,'o','oh');

2 rows updated.

SQL> select * from long_to_clob;

X
------------------------------------------------------
helloh
This is a lohng value.

Good luck.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[TStriker]

Mufasa,

The database is part of a shrink-wrapped ERP system and was designed many years ago by others; I assume back in the days before CLOB.

The ERP software is comatose as there is no new development but it still is supported by the vendor.

I'm sure that if I made structural changes to the database it would violate our support contract and we would be out-of-luck if we needed assistance.

So the bottom line is I can't do anything about it.

As for inserting carriage returns, I'm just doing without.

-Striker

 

[SantaMufasa]

...but it still is supported by the vendor.

Althouh there is "no new development", if you are paying for support, I would start negotiating with/pressuring/encouraging them to convert their LONG columns over to CLOB columns...it'll be better for them and for you.


Let us know what they'll do for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[TStriker]

I'm afraid that it would be like asking for a fresh coat of wax to be put on an automobile that is bound for the salvage yard.

The software is dead, they aren't even trying to sell it anymore. Their hope is to sell us on a different package that they offer.

-Striker