[Jimmy the Hand]

Hi All,
here's my problem.
I need to change some data in an Oracle DB of the company. One table has a field called "link", and this field contains path to files. All these files were stored on a file server, and the links worked. But then the server had been replaced, and so now the links are broken. All files had been copied to the new server, with the same folder structure. To make the links work again, I need to change the server name in some thousands of records.

In other words, if the "link" field value of a record is
\\Server1\MainFolder\SubFolder\Filename

I have to change it to
\\Server2\MainFolder\SubFolder\Filename

How can I do that?

At the moment the only interface I can use to write into the database is SQL*Plus provided by the Oracle client application. I've used only SQL statements before, so I've no experience with "batchfiles", or whatever they are called in SQL terms.
My first shot was to use the UPDATE statement but, as far as I know, it's only good for replacing the whole field value, whereas I want to replace only part of it. Can I use string functions with UPDATE? If so, how? Is there such a thing as string functions in SQL*Plus at all? Or does anyone have any better idea?

Thanks for your time and help.
Jimmy

[AGCurry]

Two possible approaches I see:
1. a. unload the table to a flat file and use sed or awk to replace the server name;
b. truncate the table;
c. load the transformed file back in using sqlldr.

2. Use the REPLACE function. I would use an anonymous PL/SQL block to do this, with a cursor looping through all the rows in the table with an UPDATE for each row.

[Jimmy the Hand]

Thanks for the help. Approach #1 is impossible, for this DB must be on all the time, and the table in question is one of the most important, most often used ones. I can't just unload it. This leaves me the 2nd approach. I think I understand what you say here, because I have a little programming background but, unfortunately, I'm not familiar with PL/SQL. Today I did some research on how it works and all, but still don't feel confident enough to risk doing something wrong. Could you give me more clues? Or a link where basics of PL/SQL are explained in detail?

[AGCurry]

There's all kinds of documentation on PL/SQL on the web, but it's about this simple:

Create a file with the following in it, call it, say, 'update_link.sql".

DECLARE
CURSOR your_curs IS
SELECT link FROM your_table
WHERE link LIKE '%Server1%'
FOR UPDATE OF link ;

BEGIN
FOR your_rec IN your_curs
LOOP
UPDATE your_table
SET link = REPLACE( rec.link, 'Server1', 'Server2' )
WHERE CURRENT OF your_curs ;
END LOOP ;
END ;
/

Then, in sqlplus,
> @update_link.sql

[Jimmy the Hand]

Thanks for the excellent code.
It didn't work at first, but then I, with my keen mind, found the error (rec.link instead of your_rec.link) and corrected it.

My next problem was that REPLACE seems to be case sensitive and 'Server1' in the link field occurred in several variations e.g. Server1, SerVer1, SErver1, etc. No problem, I modified the update_link.sql file for each existing variation, run it, and checked the result with a SELECT query. It's all right now, but I wonder, is it possible to modify your code and make it case insensitive?

And one final question. SELECT query showed that there remained 4 records that weren't updated, no matter what I did. I copied the name of Server1 from the query result into the code, so that I couldn't mistype it, but it still refused to be changed. How could this happen?

Though I asked some questions here, I'm going to mark this thread as solved, 'cos the code did what I wanted. I successfully updated 8000+ rows, which would have taken months otherwise. Thank you again.

[AGCurry]

Originally Posted by Jimmy the Hand

Thanks for the excellent code.
It didn't work at first, but then I, with my keen mind, found the error (rec.link instead of your_rec.link) and corrected it.

Glad it worked for you.
Yes, I make that kind of error all the time. I'm attentive to algorithms but not attentive enough to data names, I guess...

Originally Posted by Jimmy the Hand

My next problem was that REPLACE seems to be case sensitive and 'Server1' in the link field occurred in several variations e.g. Server1, SerVer1, SErver1, etc. No problem, I modified the update_link.sql file for each existing variation, run it, and checked the result with a SELECT query. It's all right now, but I wonder, is it possible to modify your code and make it case insensitive?

Oracle 10g has a function called ... REGEXP_REPLACE, or something like that, that would take a regular expression as the string to be replaced. That would do it. But I don't think Oracle 9g or below provides a way for REPLACE to work case-insensitively.

Originally Posted by Jimmy the Hand

And one final question. SELECT query showed that there remained 4 records that weren't updated, no matter what I did. I copied the name of Server1 from the query result into the code, so that I couldn't mistype it, but it still refused to be changed. How could this happen?

I can't really tell without closer examination, but I would think it would have to be some kind of data anomaly.