Re: PERL DBI ORACLE DATABASE OUTPUT TO A FILE

Requests for help with something that do not work are usually
accompanied by  relevant information:

Platform
versions
error messages

I did not see any of those in the post.

Please see this article, it will prove to be quite valuable to you:
http://catb.org/~esr/faqs/smart-questions.html
<http://catb.org/~esr/faqs/smart-questions.html>
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com



On Tue, May 11, 2010 at 12:25 PM, tech422 <[email protected]> wrote:

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