Home » Server Options » Text & interMedia » Re: How to replace string in CLOB?
Re: How to replace string in CLOB? [message #75863] Tue, 02 October 2001 13:22
Eric Dillon
Messages: 1
Registered: October 2001
Junior Member
You can do it fairly easily using Perl. Example script below (modify connection/table/column info and run from the command line)

#!/usr/bin/perl
use DBI;
my $db = "DBI:Oracle:your_database";
my $dbid = "your_login";
my $dbpsswd = "your_passwd";
$ENV{'ORACLE_HOME'} = '/your_path/oracle/product/8.1.6';
$ENV{'ORACLE_BIN'} = '/your_path/oracle/product/8.1.6/bin';
$dbh = DBI->connect("$db", "$dbid", "$dbpsswd") or die ("Connection to Oracle failed: $!");
$dbh->{LongTruncOk} = 1;
$dbh->{LongReadLen} = 95536; # insanely large, but oh well
#####################################################################
my ($xSQL) = "SELECT * from SCHEMA.TABLE";
my ($xsth) = $dbh->prepare($xSQL,,);
$xsth->execute || die ("Select failed for $xSQL: . $DBI::errstrn . $!");
my ($SQL) = "update SCHEMA.TABLE set COLUMN_NAME = ?";
my ($sth) = $dbh->prepare($SQL,,);
while (my $pointer = $xsth->fetchrow_hashref) {
my @holding_tank=('');
push @holding_tank, $pointer->{'COLUMN_NAME'};
foreach my $item (@holding_tank) {
if ($item =~ "string to replace") {
$item =~ s/string to replace/desired string/g;
$sth->execute
($item) || die ("Update failed for $SQL: . $DBI::errstrn . $!");
}
}
}
$sth->finish();
$xsth->finish();
$xdbh->disconnect;
warn "All Done. Check your datan";
exit;

----------------------------------------------------------------------
Previous Topic: rename column-Thanks
Next Topic: Re: sqlcxt error
Goto Forum:
  


Current Time: Thu Mar 28 06:18:36 CDT 2024