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 <craym...@bu.edu> 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); > } > >
