Can't solve Oraperl error

From: Terry Dixon <tdixon_at_rochgrp.com>
Date: 1996/11/15
Message-ID: <328D723C.311A@rochgrp.com>#1/1

Here's my definition, pretty simple, I want to get it to work before I deal with constraints. I've tried adding a zero to the "perfvalue" variable, *or* concatinating null string, or a space, just before I bind it, to see if that will effect the data-type that's inserted (to no avail). The puzzling thing; is that it works if the data is at the end of the script a la "(<DATA>)...__END__", but will not work when I use a separate input file. The print out from this script always looks great.  I can't get "bind_param" to work, either. Perhaps it has something to do with Perl storing integers as floating points. btw - Thank you.

create table perfdata_c

(location_id  char(15),
product_id    char(15),
factor_id     char(15),
month         char(6),
timeframe     char(1),
case          char(1),
perfvalue     number(15,5))

"insert" 84 lines, 1837 characters
#!/usr/bin/perl -w

##
# insert - Perl script to catch tab delimited
# data, and insert it into Oracle tables.
#
# Some code borrowed from 'DBD::Oracle' examples.
#
# T.Dixon.
##

# include external Perl Modules (/usr/local/lib/perl5/)

use diagnostics;
use Oraperl;
require ('oraperl.ph');

# initialization

use vars qw($location_id $product_id $factor_id $month

            $timeframe $case $perfvalue);

# set SQL insert string

$INSERT = "insert into perfdata_c

          (location_id, product_id, factor_id,
           month, timeframe, case, perfvalue)
          VALUES (:1, :2, :3, :4, :5, :6, :7)";

# log in to Oracle

$lda = &ora_login('ORCL', 'tdixon', 'perl') or die $ora_errstr;

die "&oralogin undefined: $|\n" unless defined &ora_login;

# open a cursor

$csr = &ora_open($lda, $INSERT) or die $ora_errstr;

# split each tab delimited column into variables
# and insert the data

while (<>)
{

   # chop off newline character from end of line

    chop;

   # get rid of the MS-DOS carraige-return (^M)

    s/^(.*)(\^M)$/$1/;

   # split the line into variables at each tab

    ($location_id, $product_id, $factor_id,      $month, $timeframe, $case, $perfvalue) =     split("\t");

   # print to STDOUT for monitoring purposes

    print "\n", join(',',

                     $location_id,
                     $product_id,
                     $factor_id,
                     $month,
                     $timeframe,
                     $case,
                     $perfvalue), "\n";

   # data inserted here

    &ora_bind($csr, $location_id, $product_id, $factor_id,

                    $month, $timeframe, $case, $perfvalue)
        or die $ora_errstr;

}

&ora_close($csr)                               or die $ora_errstr;

# log out

&ora_logoff($lda)                              or die $ora_errstr;

• MY OUTPUT:

AUSTRIA,3006,%totbrcalls,199501,A,M,50

Uncaught exception from user code:

        ORA-01722: invalid number (DBD: oexec error) at ./wcs_insert line 71, <>
 chunk 1.

-- 
Terry Dixon              mailto:tdixon_at_rochgrp.com
The Rochester Group Inc
600 Park Avenue
Rochester,NY 14607
USA                      http://rochgrp.com/

"Watch this space"

Received on Fri Nov 15 1996 - 00:00:00 CST