Re: Help with my perl scripts of dropping index

It's possible you can't drop the index as it is in use. You should check the return value of "if ( $dbh->state)" to see if it is 0 whether an index drops or not. I know nothing of using Perl with Oracle, but it *may* be that logically refusing to drop an index is not considered an actual error by the Perl interface etc...

Perhaps stick in a 'select where index still exists' and compare that after the would-be drop just as a debugging aid?

Can't help but think it's not the best idea to be looping infinitely until an index drops however...

"ff" <dbadba62_at_hotmail.com> wrote in message news:4vlW6.2796$Ua3.600116_at_typhoon2.ba-dsg.net...
> I am posting this message to this group because a lot of DBAs are good
 perl
> programmers as well. We have a perl scripts, part of it is drop index as
> follows
> ----------------------------------------------------------
> my $failure = 1;
> while ( $failure != 0 )
> {
> my $sql = qq{ drop index index_1};
> $dbh->do ( $sql );
> if ( $dbh->state == 0 )
> {
> print "index_1 dropped\n";
> $failure = 0;
> }
> else
> {
> $failure = 1;
> print "error dropping index_1= $dbh->errstr and error
 value
> $dbh->state\n";
> }
> }
> --------------------------------------------------------------------
> But the script is not very successful. One out of ten times, the index is
> not dropped and the scripts will exit the loop and continue to run, which
> will try to create the same index_1 which is suppose to be droped now.

>

>

>
>
>