Solved

# Replacing a word in all lines in file based on another field value

Posted on 2011-04-19
384 Views
Hello all,
I have a file with many lines like below and several other small lines in it. For all lines that begin with CREATE DATABASE LINK  I have to replace <PWD> (i.e field 10) with <field 7>_ppp . field7 is not constant and it varies from one line to other. It needs to be dynamically sustituted. Shown it below in detail, could some one help with me with the substitution?

Before:CREATE DATABASE LINK "abc.com"  CONNECT TO testuser  IDENTIFIED BY <PWD> USING 'abc.com';

After replace, the line should become.

CREATE DATABASE LINK "abc.com"  CONNECT TO testuser  IDENTIFIED BY testuser_ppp USING 'abc.com';

Any help will be appreciated.
0
Question by:dbguy0

LVL 23

Expert Comment

hi dbguy0,
- you can create the script line as follows:

SELECT ('CREATE DATABASE LINK ' || '''' || 'abc.com' || '''' || ' CONNECT TO testuser IDENTIFIED BY '
|| field7 || '_ppp USING ' || '''' || 'abc.com' || '''' || ';') FROM tablename


- the output will look like below. and you can copy and paste the lines to sqlplus to run it:
1. CREATE DATABASE LINK 'abc.com' CONNECT TO testuser IDENTIFIED BY UserA_ppp USING 'abc.com';
2. CREATE DATABASE LINK 'abc.com' CONNECT TO testuser IDENTIFIED BY UserB_ppp USING 'abc.com';
3. CREATE DATABASE LINK 'abc.com' CONNECT TO testuser IDENTIFIED BY UserC_ppp USING 'abc.com';
0

Author Comment

the file is already there. i need to replace them.. cant re-create the file at ths time. replacing is the only option.
0

Author Comment

i understand what you are telling mevto do. but thats using sqlplus. thats's not an option. this file is from somewhere else and i need to replace them..
0

LVL 23

Expert Comment

- so you are not allowed to paste the result of this script into a text file and replace the existing file with the new one?

- then you might have to write an Oracle PL/SQL procedure to achieve that. you can use UTL_File package to open the existing file and using the Get and Put in that package together with SQL statement to get the field7 value when writing back to the file. here is the syntax manual:
0

LVL 51

Expert Comment

i could not get the big picture :)

you have a file with these lines

CREATE DATABASE LINK "abc1.com"  CONNECT TO testuser1  IDENTIFIED BY <PWD> USING 'abc1.com';
CREATE DATABASE LINK "abc2.com"  CONNECT TO testuser2 IDENTIFIED BY <PWD> USING 'abc2.com';
CREATE DATABASE LINK "abc3.com"  CONNECT TO testuser3  IDENTIFIED BY <PWD> USING 'abc3.com';
CREATE DATABASE LINK "abc4.com"  CONNECT TO testuser4  IDENTIFIED BY <PWD> USING 'abc4.com';
CREATE DATABASE LINK "abc5.com"  CONNECT TO testuser5  IDENTIFIED BY <PWD> USING 'abc5.com';

and you want to replace <PWD> with something... different for each line? where are these <PWD>? in a database? if yes, which <PWD> goes into which line? Whats the relation?
0

Author Comment

sorry, i have no clue why this was posted to oracle scripting forum. i posted it in shell scripting forum. i have a file with around 300 lines like this. need a script which will do the replace job instead of doing it manually for 300 lines ..
0

Author Comment

lets forget the database . this is just a text file and i need to replace these..
0

Author Comment

now please read my qsn again :) if you don't mind. you will understand what i am asking. thanks.
0

LVL 51

Expert Comment

replace <PWD> on each line with what? if you have 300 lines where is the 300 <PWD>?
0

Author Comment

each line <PWD> should be replaced by fields_ppp

if field7 = testuser <PWD> should be replaced by testuser_ppp
if field 7 = abc <PWD> should be replaced by abc_ppp
if field 7 = example <PWD> should be replaced by example_ppp

and so on... above are just examples. field7 can be anything.

0

LVL 51

Expert Comment

ok here it is...

in.txt

CREATE DATABASE LINK "abc1.com" CONNECT TO testuser1 IDENTIFIED BY <PWD> USING 'abc1.com';
CREATE DATABASE LINK "abc2.com" CONNECT TO testuser2 IDENTIFIED BY <PWD> USING 'abc2.com';
CREATE DATABASE LINK "abc3.com" CONNECT TO testuser3 IDENTIFIED BY <PWD> USING 'abc3.com';
CREATE DATABASE LINK "abc4.com" CONNECT TO testuser4 IDENTIFIED BY <PWD> USING 'abc4.com';
CREATE DATABASE LINK "abc5.com" CONNECT TO testuser5 IDENTIFIED BY <PWD> USING 'abc5.com';

create a file "replace.vbs", code below, modify the paths
run it on command line, it will generate this in out.txt file

CREATE DATABASE LINK "abc1.com" CONNECT TO testuser1 IDENTIFIED BY testuser1 USING 'abc1.com';
CREATE DATABASE LINK "abc2.com" CONNECT TO testuser2 IDENTIFIED BY testuser2 USING 'abc2.com';
CREATE DATABASE LINK "abc3.com" CONNECT TO testuser3 IDENTIFIED BY testuser3 USING 'abc3.com';
CREATE DATABASE LINK "abc4.com" CONNECT TO testuser4 IDENTIFIED BY testuser4 USING 'abc4.com';
CREATE DATABASE LINK "abc5.com" CONNECT TO testuser5 IDENTIFIED BY testuser5 USING 'abc5.com';

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set outFile = objFSO.CreateTextFile("D:\MS\EE\vba\out.txt")

Do While Not inFile.AtEndOfStream
f = split(line, " ")
outFile.writeline(f(0) & " " & f(1) & " " & f(2) & " " & f(3) & " " & f(4) & " " & f(5) & " " & f(6) & " " & f(7) & " " & f(8) & " " & f(6) & " " & f(10) & " " & f(11))
Loop

inFile.close
outFile.close

0

LVL 24

Expert Comment

Or if you have a text editor that supports find and replace using regular expressions, your find expression is

^CREATE DATABASE LINK (\S+)\s+CONNECT TO (\S+)\s+IDENTIFIED BY \S+

and your replacement expression is something like:

CREATE DATABASE LINK $1 CONNECT TO$2 IDENTIFIED BY $2_ppp Should be easy to put the same in a perl script. Read the file line by line. Then process a line via line =~ s/^CREATE DATABASE LINK (\S+)\s+CONNECT TO (\S+)\s+IDENTIFIED BY \S+/CREATE DATABASE LINK$1 CONNECT TO $2 IDENTIFIED BY$2_ppp/;

0

Author Comment

need a complete script using perl or sed or awk
0

LVL 24

Expert Comment

#!/usr/bin/perl

open INPUT, "input.txt" or die $1; open OUTPUT, ">output.txt" or die$1;

while (<INPUT>) {
$line =$_;
$line =~ s/^CREATE DATABASE LINK (\S+)\s+CONNECT TO (\S+)\s+IDENTIFIED BY \S+/CREATE DATABASE LINK$1 CONNECT TO $2 IDENTIFIED BY$2_ppp/;
print OUTPUT $line; } close INPUT; close OUTPUT;  Replace input.txt and output.txt accordingly 0 LVL 24 Accepted Solution Realized it could have been simpler: #!/usr/bin/perl open INPUT, "input.txt" or die$1;

open OUTPUT, ">output.txt" or die $1; while (<INPUT>) {$line = $_;$line =~ s/CONNECT TO (\S+)\s+IDENTIFIED BY <PWD>/CONNECT TO $1 IDENTIFIED BY$1_ppp/;
print OUTPUT $line; } close INPUT; close OUTPUT;  0 Author Comment hi some reason it did not work, new file still has <PWD> ... 0 LVL 24 Expert Comment 0 LVL 24 Expert Comment oops it got submitted too soon. I don't know what's happening on your side but here are my files. Just rename replace.txt to replace.pl since EE won't allow me to attach with the .pl extension dbguy0.zip 0 LVL 24 Expert Comment I realized there might be a difference in spacing and case and my code was made only to handle the exact format you gave in your original post. Try this: #!/usr/bin/perl open INPUT, "input.txt" or die$1;

open OUTPUT, ">output.txt" or die $1; while (<INPUT>) {$line = $_;$line =~ s/CONNECT\s+TO\s+(\S+)\s+IDENTIFIED\s+BY\s+<PWD>/CONNECT TO $1 IDENTIFIED BY$1_ppp/i;
print OUTPUT \$line;
}

close INPUT;
close OUTPUT;

0

LVL 48

Expert Comment

This is very simple.

If you are on a Linux box, do
sed -i "s/$$.*CONNECT TO$$$$[a-z]*$$$$.*IDENTIFIED BY$$<PWD>/\1\2\3\2_ppp/" file


On a *nix box without GNU sed, do:

sed "s/$$.*CONNECT TO$$$$[a-z]*$$$$.*IDENTIFIED BY$$<PWD>/\1\2\3\2_ppp/" file >/tmp/$$&& mv /tmp/$$ file

0

## Featured Post

### Suggested Solutions

Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.