[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-19
20
Medium Priority
?
386 Views
Last Modified: 2012-05-11
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
Comment
Question by:dbguy0
  • 8
  • 6
  • 3
  • +2
20 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35429858
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

Open in new window


- 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

by:dbguy0
ID: 35429901
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

by:dbguy0
ID: 35429902
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35429932
- 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:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35429935
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

by:dbguy0
ID: 35429942
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

by:dbguy0
ID: 35429949
lets forget the database . this is just a text file and i need to replace these..
0
 

Author Comment

by:dbguy0
ID: 35429951
now please read my qsn again :) if you don't mind. you will understand what i am asking. thanks.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35429980
replace <PWD> on each line with what? if you have 300 lines where is the 300 <PWD>?
0
 

Author Comment

by:dbguy0
ID: 35429993
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 61

Expert Comment

by:HainKurt
ID: 35430033
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 inFile = objFSO.OpenTextFile("D:\MS\EE\vba\in.txt", ForReading)
Set outFile = objFSO.CreateTextFile("D:\MS\EE\vba\out.txt")

Do While Not inFile.AtEndOfStream
    line = inFile.readline
    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

Open in new window

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35430086
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

by:dbguy0
ID: 35430102
need a complete script using perl or sed or awk
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35430145
#!/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;

Open in new window


Replace input.txt and output.txt accordingly
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 35430178
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;

Open in new window

0
 

Author Comment

by:dbguy0
ID: 35430356
hi some reason it did not work, new file still has <PWD> ...
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35430366
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 35430371
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

by:johanntagle
ID: 35430922
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;

Open in new window

0
 
LVL 48

Expert Comment

by:Tintin
ID: 35431266
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

Open in new window


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

Open in new window

0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question