Skip to content

Instantly share code, notes, and snippets.

@BastienDurel
Created February 19, 2014 11:34
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save BastienDurel/9090329 to your computer and use it in GitHub Desktop.
Save BastienDurel/9090329 to your computer and use it in GitHub Desktop.
DBD::Oracle::db do failed: ORA-14452: attempt to create, alter or drop an index on temporary table already in use (DBD ERROR: error possibly near <*> indicator at char 15 in 'drop table SYS.<*>test_foobar') [for Statement "drop table SYS.test_foobar"] at test-dbi-oracle.pl line 17.
#!/usr/bin/perl
use strict;
use warnings;
use DBI qw(:sql_types);
use DBD::Oracle qw(:ora_session_modes);
my $run = 1;
$SIG{INT} = sub { $run = 0; print "Stopping ...\n"; };
my $dbh = DBI->connect("dbi:Oracle:", "", "",
{ RaiseError => 1, ora_session_mode => ORA_SYSDBA }) or die 'Can\'t connect!';
sub foo {
eval {
$dbh->{RaiseError} = 0;
$dbh->do(q{drop table SYS.test_foobar});
};
$dbh->{RaiseError} = 1;
my $sql = qq{create global temporary table SYS.test_foobar ON COMMIT PRESERVE ROWS as
select * from ALL_TAB_COMMENTS };
$dbh->do($sql);
my $sth = $dbh->prepare(q{
SELECT
TABLE_NAME
FROM
SYS.test_foobar
});
$sth->execute;
while (my $row = $sth->fetchrow_arrayref) {
# Some work
}
$sth->finish;
}
while ($run) {
sleep 1;
foo();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment