PERL DBI ORACLE DATABASE OUTPUT TO A FILE

[tech422]

Hi,

I am trying to query an Oracle database and output the results to a
file using PERL but its not working. I have tried 2 approaches. 1 uses
bind the other does not. Could you please advise?:


Using Bind:
sub get_cms {

  $ENV{ORACLE_HOME} = "/usr/local/oracle/OraHome1";
  my $unique = 0;

open(DIROUT,'>',"myNewCMSOutputFile.csv") || die("Unable to open
output file named myNewCMSOutputFile.csv");
   my $dbh = DBI-
>connect( 
>"dbi:Oracle:host=nsegdb01.bu.edu;sid=PRODLX;port=1522",'production','pcms10lx')
|| die "Database connecti
on not made: $DBI::errstr";

$query = "Select p_devices.NAME DEVICE_NAME, P_Devices.IP_ADDRESS
DEV_IP_ADDRESS, P_Gateways.NAME GATEWAY_NAME, P_Gateways.IP_ADDRES
S GATEWAY_IP_ADDRESS, P_subnets.MASK SUBNET_MASK, P_subnets.NOTATION
SUBNET_NOTATION, P_subnets.NAME SUBNET_NAME, P_Vlans.VLAN_NO VL
AN_NO from p_devices, p_gateways, p_ports, p_subnets, p_vlans where
p_ports.DEV_ID = p_devices.ID and p_ports.GW_ID = p_gateways.ID
and p_ports.SBNT_ID = p_subnets.ID and p_ports.VLAN_ID = p_vlans.ID";
  $sth = $dbh -> prepare($query);
  $sth -> execute;
  my($col1,$col2,$cms_gateway_name,$cms_gateway_ip_address,
$cms_subnet_mask,$cms_subnet_notation,$cms_subnet_name,$cms_vlan_no);
  $sth->bind_columns(\($col1,$col2,$cms_gateway_name,
$cms_gateway_ip_address,$cms_subnet_mask,$cms_subnet_notation,
$cms_subnet_name,
$cms_vlan_no));

  while ( $sth->fetch)
 {
 print "$cms_gateway_name,$cms_gateway_ip_address,$cms_subnet_mask,
$cms_subnet_name,$cms_vlan_no\n";
 $myNewString = $cms_gateway_name.",".$cms_gateway_ip_address.",".
$cms_subnet_mask.",".$cms_subnet_name.",".$cms_vlan_no;
$myNewFile = $myNewFile.$myNewString."\n";
 }
print DIROUT $myNewFile;
close(DIROUT);
}


Not using Bind:

sub get_cms {

  $ENV{ORACLE_HOME} = "/usr/local/oracle/OraHome1";
  my $unique = 0;

open(DIROUT,'>',"myNewCMSOutputFile.csv") || die("Unable to open
output file named myNewCMSOutputFile.csv");
   my $dbh = DBI-
>connect( 
>"dbi:Oracle:host=nsegdb01.bu.edu;sid=PRODLX;port=1522",'production','pcms10lx')
|| die "Database connecti
on not made: $DBI::errstr";

$query = "Select p_devices.NAME DEVICE_NAME, P_Devices.IP_ADDRESS
DEV_IP_ADDRESS, P_Gateways.NAME GATEWAY_NAME, P_Gateways.IP_ADDRES
S GATEWAY_IP_ADDRESS, P_subnets.MASK SUBNET_MASK, P_subnets.NOTATION
SUBNET_NOTATION, P_subnets.NAME SUBNET_NAME, P_Vlans.VLAN_NO VL
AN_NO from p_devices, p_gateways, p_ports, p_subnets, p_vlans where
p_ports.DEV_ID = p_devices.ID and p_ports.GW_ID = p_gateways.ID
and p_ports.SBNT_ID = p_subnets.ID and p_ports.VLAN_ID = p_vlans.ID";
  $sth = $dbh -> prepare($query);
  $sth -> execute;

  while ( $data = $sth->fetchrow_hashref() )
 {

     print $data->{GATEWAY_NAME};
     #$cms_gateway_name = $data->{GATEWAY_NAME};
     #print $cms_gateway_name;
     print ",";

     print $data->{GATEWAY_IP_ADDRESS};
     #$cms_gateway_ip_address = $data->{GATEWAY_IP_ADDRESS};
     #print $cms_gatway_ip_address;
     print ",";

     print $data->{SUBNET_MASK};
     #$cms_subnet_mask = $data->{SUBNET_MASK};
     #print $cms_subnet_mask;
     print ",";

     print $data->{SUBNET_NAME};
     #$cms_subnet_name = $data->{SUBNET_NAME};
     #print $cms_subnet_name;
     print ",";

     print $data->{VLAN_NO};
     #$cms_vlan_no = $data->{VLAN_NO};
     #print $cms_vlan_no;
     print "\n";

     $myNewString = $cms_gateway_name.",".$cms_gateway_ip_address.",".
$cms_subnet_mask.",".$cms_subnet_name.",".$cms_vlan_no;
     $myNewFile = $myNewFile.$myNewString."\n";
     #print $myNewFile;
     #print DIROUT $myNewFile;
  }
#print $myNewFile;
print DIROUT $myNewFile;
close(DIROUT);
}