perl执行ORACLE存储过程报错！

本帖最后由 leoxqing 于 2010-08-21 16:54 编辑

check_oracle_tablespace.pl

#!/usr/bin/perl -w
use DBI;
use DBD::Oracle;
use File::Basename;
use Getopt::Long;
$progname=basename($0);
sub print_help();
sub print_usage();
use vars qw (
                        $opt_help
                        $opt_host
                        $opt_sid
                        $opt_user
                        $opt_password
                        );
Getopt::Long::Configure('bundling');
GetOptions (
        "h"        =>        \$opt_help,        "help"                =>        \$opt_help,
                                        "usage"                =>        \$opt_usage,
        "H=s"        =>        \$opt_host,        "host=s"        =>        \$opt_host,
        "S=s"        =>        \$opt_sid,        "sid=s"                =>        \$opt_sid,
        "U=s"        =>        \$opt_user,        "user=s"        =>        \$opt_user,
        "P=s"        =>        \$opt_password,        "password=s"        =>        \$opt_password
  ) || die "Try '$progname --help' for more information.\n";
my %ERRORS = ('OK' , 0,
              'WARNING', 1,
              'CRITICAL', 2,
              'UNKNOWN',3);
sub print_help()
{
        print "\n";
        print "HELP for $progname:\n\n";
        print "\t --help, -h\t\t prints this Message.\n";
        print "\t --usage\t\t prints a little Usage.\n";
        print "\n"; print "\t --host, -h\t\t the host for remote oracle.\n";
        print "\n"; print "\t --sid, -S\t\t the SID for remote oracle.\n";
        print "\n"; print "\t --user, -U\t\t the username for remote oracle.\n";
        print "\n"; print "\t --password, -P\t\t the password for remote oracle.\n";
        print "\n";
}

sub print_usage()
{
        print "\n";
        print "USAGE:\n";
        print "./check_oracle_tablespace -H 127.0.0.1 -S ORCL -U username -P password\n";
        print "\n";
        print "Check the oracle tablespace.\n";
        print "\n";
}
if ($opt_help)
{
print_help();
exit;
}
elsif ($opt_usage)
{
print_usage();
exit;
}
elsif ($opt_host && $opt_sid && $opt_user && $opt_password)
{
#my $dbname="BOSSDATA";
#my $user="sys";
#my $passwd="sjorc1";
my $dbh="";
$dbh = DBI->connect("DBI:Oracle:host=$opt_host;sid=$opt_sid",$opt_user,$opt_password)or die "can't connect to database ";
#$dbh = DBI->connect("DBI:Oracle:host=$opt_host;sid=$opt_sid",$opt_user,$opt_password,{ora_session_mode => ORA_SYSDBA}) or die "can't connect to database ";
my ($status,$msg);
my $sql1=qq{begin
                 pk_ocp_database_monitor.ps_tablespace_monitor(:param1,:param2);
            end;};
$sth=$dbh->prepare($sql1);
$sth->bind_param_inout(":param1",\$msg,500);
$sth->bind_param_inout(":param2",\$status,2);
$sth->execute;
$dbh->disconnect;
print "Oracle Machine $opt_host check:$msg\n";
exit $status;
}
else
{
print_help();
exit;
}

写了个nagios监控插件，用perl调用ORACLE存储过程，用sysdba用户执行就正常，用oracle普通用户权限执行就报错，错误信息如下：
[root@leoxqing ~]# ./check_oracle_tablespace.pl -H 192.168.10.203 -S dfe -U ms -P rds
DBD::Oracle::st execute failed: ORA-06550: line 2, column 5:
PLS-00201: identifier 'PK_OCP_DATABASE_MONITOR.PS_TABLESPACE_MONITOR' must be declared
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 10 in 'begin
                 <*>pk_ocp_database_monitor.ps_tablespace_monitor(:param1,:param2);
            end;') [for Statement "begin
                 pk_ocp_database_monitor.ps_tablespace_monitor(:param1,:param2);
            end;" with ParamValues: :param1=undef, :param2=undef] at ./check_oracle_tablespace.pl line 76.
Use of uninitialized value in concatenation (.) or string at ./check_oracle_tablespace.pl line 78.
Oracle Machine 192.168.70.206 check:
Use of uninitialized value in exit at ./check_oracle_tablespace.pl line 79.

不知道何原因，本人对ORACLE不熟，忘大侠们指教！

回复 leoxqing


    在普通用户下能手工执行pk_ocp_database_monitor.ps_tablespace_monitor这个吗？

以普通用户登录ORACLE单独执行存储过程是可以通过的！