RE: PERL DBI ORACLE DATABASE OUTPUT TO A FILE

I like using Text::CSV_XS for this sort of thing.  If your columns in your select are ordered properly you could try the following.  

use Text::CSV_XS;
use IO::File;

my $csv = Text::CSV_XS->new({binary=>1, eol=>$/, always_quote=>1});
my $fh = IO::File->new(">somefile.csv");

if(defined $fh){
  my $sth = $db->prepare($query) || die $db->errstr;
  $sth->execute() || die $db->errstr;

  #-- be a little creative and write your column headers to the file.
  my @header = @{$sth->{NAME_lc});
  $csv->print($fh,\@header);

  while(my $rec = $sth->fetchrow_arrayref()){
    $csv->print($fh,$rec);
  } 

  $fh->close;
}
else{
  die "$!: somefile.csv";
}




-----Original Message-----
From: tech422 [mailto:[email protected]] 
Sent: Tuesday, May 11, 2010 1:25 PM
To: [email protected]
Subject: PERL DBI ORACLE DATABASE OUTPUT TO A FILE

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);
}