perlquestion
rbc
Dear Monks, <BR>
I don't know what I am doing wrong here.<BR>
I think the white board marker fumes are giving<BR>
me a mighty buzz because I cannot figure this<BR>
problem I am having out.<BR>
<BR>
The below script works fine. It pulls the source code<BR>
out of a Oracle database for FUNCTIONS, PROCEDURES,<BR>
PACKAGES and PACKAGE BODYS <BR>
<readmore>
<code>
#!/usr/bin/perl -w
use DBI;
$NAME = shift;
$TYPE = shift;
$dbname = 'prod';
$dbuser = 'prod';
$dbpass = 'prod';
$dbd = 'Oracle';
$dbh = DBI->connect ( $dbname, $dbuser, $dbpass, $dbd);
if (!$dbh) {
print "Error conecting to DB; $DBI::errstr\n";
}
my $sql = <<SQL;
select
text
from
user_source
where
name = ?
and type = ?
SQL
my $sth = $dbh->prepare($sql) || die $dbh->errstr;
print "CREATE or REPLACE\n";
$sth->execute( $NAME, $TYPE );
while ( my @r = $sth->fetchrow_array ) {
print @r;
}
print "/\n";
print "show errors\n";
$sth->finish || die;
$dbh->disconnect;
</code>
... I thought that I would modify this script<BR>
so that I could get the source code for triggers.<BR>
And here's the trigger getting script which does not<BR>
seem to get any trigger source!<BR>
<code>
#!/usr/bin/perl -w
use DBI;
use Data::Dumper;
$NAME = shift;
$dbname = 'prod';
$dbuser = 'prod';
$dbpass = 'prod';
$dbd = 'Oracle';
$dbh = DBI->connect ( $dbname, $dbuser, $dbpass, $dbd);
if (!$dbh) {
print "Error conecting to DB; $DBI::errstr\n";
}
my $sql = <<SQL;
select
description,
trigger_body
from
user_triggers
where
trigger_name = ?
SQL
#print $sql;
my $sth = $dbh->prepare($sql) || die $dbh->errstr;
print "CREATE or REPLACE $NAME\n";
$sth->execute( $NAME );
while ( my @r = $sth->fetchrow_array ) {
print @r;
}
print "/\n";
print "show errors\n";
$sth->finish || die;
$dbh->disconnect;
</code>
When I run this above script like so ...<BR>
<pre>
$ ./getTrigger.pl MY_TRIGGER
</pre>
<BR>
... the only output I get is this ...<BR>
<pre>
CREATE or REPLACE MY_TRIGGER
/
show errors
</pre>
And there is a trigger named MY_TRIGGER in my DB. :(<BR>
I must be missing something. <BR>
Your help is appreicated!