To see if the problem's in the Oracle side or the DBI side, does the SQL
statement run directly from SQL*Plus on the client? Have you tried using
the WE8ISO8859P1 NLS_LANG setting? Don't use the EBCDIC one on HP -- it
won't work.
A few other questions/points. Why are you using TO_CHAR? Or, instead of
having Perl do your accumulating, you could be using the SQL SUM and COUNT
functions. Like this:
"select to_char(SUM(sea_temperature),'99999'),
count(*) from midasvu.marine_ob ".
"where OB_TIME between
to_date('01-JAN-1989 0000',".
"'dd-mon-yyyy hh24mi') and to_date('01-JAN-1989 2359',".
"'dd-mon-yyyy hh24mi')";
Also, when using 8-bit charsets, you'll need to make sure that your term on
HP is set up to handle it. You'll need at least "stty cs8 -istrip".
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
> -----Original Message-----
> From: Birchall, Austen [mailto:austen.birchall@;metoffice.com]
> Sent: Thursday, October 17, 2002 4:16 AM
> To: 'Birchall, Austen'; [EMAIL PROTECTED]
> Subject: RE: HP-UX - Perl DBI - mainframe issue
>
>
> Following Davids suggestion (thanks) this is the code that is
> causing the
> problem - please note I am very much the 'middle man' here
> but if anyone has
> any further suggestions/questions I can pass them on to the developer
>
> Thanks
>
> Austen
>
>
> #!/usr/bin/perl -w
>
> # Connect to MIDAS
> #
> # Note that this script requires a range of Oracle
> Environment variables to
> # be set before starting, and will only work on workstations
> where the
> # oracle client software is installed. I.e. hc1900.
> #
>
> use strict;
> use DBI;
>
> # Choose production (orap) or test (orat) databases
> $ENV{TWO_TASK}='orap';
> #$ENV{'NLS_LANG'}='american_america.we8iso8859p1';
> #$ENV{'NLS_LANG'}='american_america.we8ebcdic1047';
>
> # Get user name and password from users .netrc
> my ($UserName,$Password);
> open(DIN,"$ENV{HOME}/.netrc") or die "Can't open $ENV{HOME}/.netrc";
> while(<DIN>) {
> my @fields=split;
> if($fields[1] eq 'ukmet') {
> $UserName = $fields[3];
> $Password = $fields[5];
> }
> }
> close(DIN);
> unless( defined($UserName) && defined($Password)) {
> die "Failed to extract COSMOS username and password from
> $ENV{HOME}/.netrc";
> }
>
> # Connect to the database
> my $dbh = DBI->connect("DBI:Oracle:",
> $UserName,$Password,
> {PrintError => 1, RaiseError => 1}) or
> die $DBI::errstr;
>
> # Do some processing - let's get some marine data.
> my $Sql_statement = "select to_char(sea_temperature,'99999') from
> midasvu.marine_ob ".
> "where OB_TIME between
> to_date('01-JAN-1989 0000',".
> "'dd-mon-yyyy hh24mi') and to_date('01-JAN-1989 2359',".
> "'dd-mon-yyyy hh24mi')";
>
> my $Statement = $dbh->prepare($Sql_statement);
> $Statement->execute();
>
> # Process the results - let's calculate the mean.
> my $count = 0;
> my $Accumulate=0;
> while(my @sstA = $Statement->fetchrow_array()) {
> unless(defined($sstA[0])) { next; }
> $Accumulate += $sstA[0];
> $count++;
> }
> $Statement->finish();
>
> printf "Mean SST was %5.2f, from %6d
> measurements\n",$Accumulate/$count,$count;
>
> # Close the database connection
> $dbh->disconnect();
> exit(0);