Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\scripts\has\SQL.pl

#
# Copyright (c) 2001, 2008, Oracle. All rights reserved.  
#
#  $Id: SQL.pl 08-apr-2008.14:51:00 rsamaved Exp $ 
#
#
# NAME  
#	 SQL.pm
#
# DESC 
#	 SQL routines used , different between 10.2.0.4 and 11 as
#         11 uses jdbc_oci_connector and 10.2.0.4 uses DBI
#
#
# FUNCTIONS
# AUTOLOADER
#
# NOTES
#
#
# MODIFIED	(MM/DD/YY)
# rsamaved       04/08/08 - 
# ajdsouza       01/23/07 - Created 
#
#
use DBI;
#require "jdbc_oci_connector.pl";

#------------------------------------------------------------------------------
# FUNCTION :    hasGetSQLResults
#
# DESC
#  return FALSE or HAS Name
#
# ARGUMENTS
# ref to hash of credentials 
#  $credentialsRef =
#  {
#    username =>'system',
#    password => 'manmager',
#    address => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<dbhostname>)(Port=<port>))(CONNECT_DATA=(SID=<sid>)))',
#    role => SYSDBA|SYSOPER,
#    oracle_home => '/u01/oracle'
#    sql => 
#     [ ( sql1, sql2) ]
# # a list of sqls to execute
# DB connection timeout and retry values in secs, optional
#    db_timeout  => 10;   #timeout secs
#    db_retry    => 3;    #number of retries
#    db_waittime => 60;   #wait time before retry
#  };
#
# ref to hash with result field order details  by sql
#   $orderOfFieldsRef = { sql1 => { 'col1' =>1 , 'col2' =>2 }
#                         sql2 => {  'col1' =>1 , 'col2' =>2 } } ;
#  this arg is optional. If this arg is passed then results are returned with each result row as hash
#   without this arg each row is returned as a list
#
# RETURNS
#  an ref to an has by sql
#   each value for a sql gives the result list for that sql
#   where each element in the list is a ref to the result row
#   the result row is either a has if orderOfFieldsRef is passed else a list
#------------------------------------------------------------------------------
#sub hasGetSQLResults($;$)
#{
#   my ( $credentialsRef,$orderOfFieldsRef ) = @_;
#
#   warn "WARN:has::Common::hasGetSQLResults: Argumentis username/password/address/sql not provided \n" 
#    and return unless $credentialsRef and ref($credentialsRef) and ref($credentialsRef) =~ /HASH/ 
#    and keys %{$credentialsRef}; 
#
#   # validate the list of fields to be in the hash for credentials
#   my %validlist = ( 1 => 'username' , 2 => 'address', 3=>'password', 4 =>'sql' );
#   my %cred;
#
#   for my $order ( sort  {$a <=> $b} keys %validlist ) 
#   {
#     my $val;
#
#     for my $cval ( keys %{$credentialsRef} )
#     {
#         next unless $cval and $validlist{$order} =~ /^$cval$/i;
#
#         $val = $cval and last;
#     }
#
#     warn "WARN:has::Common::hasGetSQLResults: Argument $validlist{$order} not provided \n" 
#      and return unless $val; 
#
#     $cred{$validlist{$order}} = $credentialsRef->{$val};
#
#   }
#
#   my $un = $cred{username};
#   my $pw = $cred{password};
#   my $ad = $cred{address};
#
#   my $mode;
#   $mode = 0;
#   $mode = 2 if $credentialsRef->{role} and $credentialsRef->{role} =~ /SYSDBA/i;
#   $mode = 4 if $credentialsRef->{role} and $credentialsRef->{role} =~ /SYSOPER/i;
#
#   # DB connection timeout and retry values in secs
#   my $db_timeout  = $credentialsRef->{db_timeout} if defined $credentialsRef->{db_timeout};   #timeout secs
#   my $db_retry    = $credentialsRef->{db_retry} if defined $credentialsRef->{db_retry};   #number of retries
#   my $db_waittime = $credentialsRef->{db_waittime} if defined $credentialsRef->{db_waittime};   #wait time before retry
#
#   $db_timeout  = 60 unless defined $db_timeout;   #timeout secs
#   $db_retry    = 3 unless defined $db_retry;    #number of retries
#   $db_waittime = 5 unless defined $db_waittime;   #wait time before retry
#
#   EMD_PERL_DEBUG ("Connecting to $un $ad \n");
#
#   #--------------------------------
#   # Get connection to the database
#   #--------------------------------
#   my %connection;
#   $connection{"db_conninfo"} = $ad;
#   $connection{"db_username"} = $un;
#   $connection{"db_password"} = $pw;
#   $connection{"db_mode"} = $mode;
#   #$connection{"db_raiseerror"} = 1;
#   #$connection{"db_printerror"} = 1;
#
#   my %results_arr_ref;
#
#   jdbc_oci_connect(\%connection)
#     or warn "WARN:has::Common::hasGetSQLResults: Could not connect to $un/$ad: ".jdbc_oci_returnErrStr(\%connection)
#     and return;
#
#   jdbc_oci_setFetchHashKeyName(\%connection,'NAME_lc' );
#
#   # loop over the array of sql and execute them and return the results in a hash
#   for my $sql ( @{$cred{sql}} )
#   {
#   my $array_ref;
#
#   # initialize the results array
#   $results_arr_ref{$sql}=$array_ref;
#
#   # get the array for the field order , from the sql based hash
#   my $fieldOrderRef;
#   $fieldOrderRef = $orderOfFieldsRef->{$sql} if $orderOfFieldsRef and $orderOfFieldsRef->{$sql};
#
#   #---------------------------------------------------------------------------------
#   # Execute the sql ad fetch the results as an array of hashes
#   #---------------------------------------------------------------------------------
#   my $sth = jdbc_oci_prepare(\%connection, $sql) 
#    or warn "WARN:has::Common::hasGetSQLResults: preparing $sql\n" and next;
#
#   jdbc_oci_execute(\%connection, $sth) 
#    or jdbc_oci_finish(\%connection,$sth) 
#    and warn "WARN:has::Common::hasGetSQLResults : executing $sql\n" and next;
#
#   $array_ref = jdbc_oci_fetchall_arrayref(\%connection,$sth, $fieldOrderRef) 
#    or jdbc_oci_finish(\%connection,$sth) 
#    and warn "WARN:has::Common::hasGetSQLResults: fetching $sql \n" and next;
#
#   warn "WARN:has::Common::hasGetSQLResults : $sql fetch :: ".jdbc_oci_returnErrStr(\%connection, $sth)." \n" 
#    and jdbc_oci_finish(\%connection,$sth) and next
#    if jdbc_oci_returnErr(\%connection, $sth);
#
#   # this check is not required refer bug# 4764143, there can be queries that fetch no results
#   # die "ERROR : $sql No rows found \n" and $sth->finish and $dbh{dbh}->disconnect unless $sth->rows and $array_ref and @{$array_ref};
#   if ( not $sth->rows or not $array_ref or not @{$array_ref} )
#   {
#     jdbc_oci_finish(\%connection,$sth);
#
#   }
#   else
#   {
#
#      jdbc_oci_get_rows(\%connection, $sth) 
#       or warn "WARN:has::Common::hasGetSQLResults failed to get rows in jdbc_oci_get_rows for $sql" 
#       and next; 
#  
#      $results_arr_ref{$sql}=$array_ref;
#  
#      jdbc_oci_finish(\%connection,$sth) or 
#       warn "WARN:has::Common::hasGetSQLResults to disconnect from the database $ad in jdbc_oci_finish for $sql" 
#       and next;   
#
#    }
#
#  }
#
#  jdbc_oci_disconnect(\%connection) or return;
#
#  return \%results_arr_ref; 
#
#}

sub hasGetSQLResults($;$)
{
   my ( $credentialsRef,$orderOfFieldsRef ) = @_;

warn "WARN:has::Common::hasGetSQLResults: Argumentis username/password/address/sql not provided \n" 
    and return unless $credentialsRef and ref($credentialsRef) and ref($credentialsRef) =~ /HASH/ 
    and keys %{$credentialsRef};

# validate the list of fields to be in the hash for credentials
   my %validlist = ( 1 => 'username' , 2 => 'address', 3=>'password', 4 =>'sql' );
   my %cred;

for my $order ( sort  {$a <=> $b} keys %validlist ) 
   {
     my $val;

for my $cval ( keys %{$credentialsRef} )
     {
         next unless $cval and $validlist{$order} =~ /^$cval$/i;

$val = $cval and last;
     }

warn "WARN:has::Common::hasGetSQLResults: Argument $validlist{$order} not provided \n" 
      and return unless $val;

$cred{$validlist{$order}} = $credentialsRef->{$val};

}

my $un = $cred{username};
   my $pw = $cred{password};
   my $ad = $cred{address};

my $mode;
   $mode = 0;
   $mode = 2 if $credentialsRef->{role} and $credentialsRef->{role} =~ /SYSDBA/i;
   $mode = 4 if $credentialsRef->{role} and $credentialsRef->{role} =~ /SYSOPER/i;

# DB connection timeout and retry values in secs
   my $db_timeout  = $credentialsRef->{db_timeout} if defined $credentialsRef->{db_timeout};   #timeout secs
   my $db_retry    = $credentialsRef->{db_retry} if defined $credentialsRef->{db_retry};   #number of retries
   my $db_waittime = $credentialsRef->{db_waittime} if defined $credentialsRef->{db_waittime};   #wait time before retry

$db_timeout  = 60 unless defined $db_timeout;   #timeout secs
   $db_retry    = 3 unless defined $db_retry;    #number of retries
   $db_waittime = 5 unless defined $db_waittime;   #wait time before retry

EMD_PERL_DEBUG ("Connecting to $un $ad \n");

#--------------------------------
   # Get connection to the database
   #--------------------------------
   my %dbh;

my %results_arr_ref;

$dbh{dbh}= DBI->connect('dbi:Oracle:', "$un@".$ad , "$pw",{ora_session_mode => $mode, PrintError => 0, RaiseError => 0})
     or warn "WARN:has::Common::hasGetSQLResults: Could not connect to $un/$ad: $DBI::errstr"
     and return;

$dbh{dbh}->{FetchHashKeyName} = 'NAME_lc';

# loop over the array of sql and execute them and return the results in a hash
   for my $sql ( @{$cred{sql}} )
   {
     my $array_ref;

# initialize the results array
     $results_arr_ref{$sql}=$array_ref;

# get the array for the field order , from the sql based hash
     my $fieldOrderRef;
     $fieldOrderRef = $orderOfFieldsRef->{$sql} if $orderOfFieldsRef and $orderOfFieldsRef->{$sql};

#---------------------------------------------------------------------------------
     # Execute the sql ad fetch the results as an array of hashes
     #---------------------------------------------------------------------------------
     my $sth = $dbh{dbh}->prepare($sql) 
      or warn "WARN:has::Common::hasGetSQLResults: preparing $sql\n" and next;

$sth->execute 
      or $sth->finish 
      and warn "WARN:has::Common::hasGetSQLResults : executing $sql\n" and next;

$array_ref = $sth->fetchall_arrayref($fieldOrderRef) 
      or $sth->finish 
      and warn "WARN:has::Common::hasGetSQLResults: fetching $sql \n" and next;
    
     warn "WARN:has::Common::hasGetSQLResults : $sql fetch :: $sth->errstr"
      and $sth->finish 
      and next
      if $sth->err;

# this check is not required refer bug# 4764143, there can be queries that fetch no results
     if ( not $sth->rows or not $array_ref or not @{$array_ref} )
     {
      $sth->finish;

}
     else
     {

#my @results;
      #if ( $sth->rows and $array_ref and @{$array_ref} )
      #{ 
        # storage the results in a global
        #for my $row ( @{$array_ref} )
        #{ 
        #  my %rowhsh = %{$row};
        #  push @results,\%rowhsh;
        #} 
      #}
      #$results_arr_ref{$sql}=\@results;
      $results_arr_ref{$sql}=$array_ref;

$sth->finish or
       warn "WARN:has::Common::hasGetSQLResults to disconnect from the database $ad in jdbc_oci_finish for $sql" 
       and next;

}

$dbh{dbh}->disconnect or return;

return \%results_arr_ref;

}

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de