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:craym...@bu.edu] Sent: Tuesday, May 11, 2010 1:25 PM To: dbi-users@perl.org 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); }