Cacher is the code snippet organizer for pro developers

#!/usr/bin/perl
#
# desc   : dispatching delimiting SQL queries from file to multiple threads
# author : Florent Jardin
# date   : 26/02/2013

use threads;
use threads::shared;

my @queries : shared = ();
my $workers = 32;
my $regex = "^CREATE.*INDEX";

my $file = shift(@ARGV);
usage() if (!defined $file);

unlink glob("$file.*");
parser($file);

foreach (1..$workers) {
	threads->create(\&worker);
}

foreach $thr (threads->list) {
	if ($thr->tid and !threads::equal($thr, threads->self)) {
		$thr->join;
	}
}

#----------------
#-- FUNCTIONS

sub usage {
	print "\nUsage: script_name.pl sqlfile.sql\n\n";
	exit(1);
}

# match each line from $regex to ';' wildcard
# store query into shared array @queries
sub parser {
	my $file = shift;
	my $counter = -1;
	my $active = 0;
	open FILE, '<', $file;

	while (my $line = <FILE>) {
		if ($line =~ /$regex/) {
			$counter++; 
			$active = 1; 
		}
		@queries[$counter] .= $line if ($active);		
		$active = 0 if ($line =~ /;$/);
	}
	
	close FILE;
}

# consume data from shared array @queries
# execute query via sqlplus system command
sub worker {
	my $tid = threads->self->tid();
	my $nb = 0;
	
	while (@queries) {
		my $query = shift(@queries);
		my $command = join("\n", ("EOF","SET ECHO ON", "host date", $query, "EXIT;","EOF"));
		
		# system("cat >> $file.$tid.log 2>&1 <<$command");
		system("sqlplus -S / as sysdba >> $file.$tid.log 2>&1 <<$command");
		$nb++;
	}
	
	my $errors = `grep -c ORA- $file.$tid.log`;
	printf "Thread %d ran %d queries with %d errors\n", $tid, $nb, $errors;
}