ORA-06502: PL/SQL: numeric or value error: character to number conversion error /w ORA-06512 error

Hi

I have a situation where the attached script works fine with different SQL*Plus clients (8 and 9), on a variety of PCs (all W2K), with a variety of Oracle network databases (AIX and W2K: 7.3, 8.1.7, 9.0.2), but it will not work on one client machine.

The machine is a W2K PC running SQL*Plus 9.0.1.3.0 against an AIX Oracle 8.1.7 database. Once again, I can run the same script from another W2K machine, using the same SQL*Plus, against the same Oracle target, with the same Oracle login, so I am confident that the problem is related to a configuration issue on the machine where the script will not work.

The error I get on that machine is:
"ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 40"

The suspect lines seem to be:
...
subtotal := to_number(c1_rec.pamount) + to_number(c1_rec.paint);
insert into pencash_calc
    values(key_id, pdate, null, c1_rec.pmemo, null, c1_rec.payto, subtotal);
...

subtotal is a varchar with numerical assignments, and subtotal := to_char(to_number(c1_rec.pamount) + to_number(c1_rec.paint)) makes no difference, but again this script works elsewhere so I am not sure why this one machine can't run it.

If we comment out the subtotal := line and replace the field insertion with a null, the script works on the suspect machine.

I have also compared the SQL*Plus environment options, and tnsnames.ora and sqlnet.ora files between the suspect machine and a working one, and but I could find no significant differences.

Any ideas on what to look for on the suspect machine or what the underlying problem is?

Thanks and take care,
Shayne

script:
/*
SQL> desc pencash
 Name                            Null?    Type
 ------------------------------- -------- ----
 KEY                                      VARCHAR2(14)
 FDATE                                    DATE
 PDATE                                    DATE
 PAMOUNT                                  VARCHAR2(40)
 PAINT                                    VARCHAR2(40)
 PAYTO                                    VARCHAR2(40)
 PMEMO                                    VARCHAR2(40)
 PCHNO                                    VARCHAR2(8)
*/
/*
SQL> desc pencash_calc;
 Name                            Null?    Type
 ------------------------------- -------- ----
 KEY_ID                                   VARCHAR2(40)
 PDATE                                    DATE
 GROSSAMT                                 VARCHAR2(40)
 PMEMO                                    VARCHAR2(50)
 PER_CENT                                 VARCHAR2(40)
 PAYTO                                    VARCHAR2(40)
 SUBTOTAL                                 VARCHAR2(80)
*/

truncate table pencash_calc;

DECLARE

   CURSOR c1 IS
      SELECT
       p.key,
      p.pdate,
        p.pmemo,
      p.payto,
      p.pamount,
        p.paint
        FROM pencash p      
--      group by p.key, p.date  
        ORDER BY p.key;


  c1_rec                 c1%ROWTYPE;
  w_sql_code             number := 0;
  w_sql_mssg             varchar2(79);
  gross_amt             varchar2(40) := 0;
  Key_id             varchar2(10);
  pdate                   date;
  temp_Key             varchar2(10);
  temp_pdate             date;
  temp_pmemo             varchar2(50);
  temp_payto             varchar2(9);
  subtotal             varchar2(80);

BEGIN

  OPEN c1;

  FETCH c1 into c1_rec;
  temp_key := c1_rec.key;
  temp_pdate := c1_rec.pdate;

  LOOP
    EXIT WHEN c1%NOTFOUND;
    key_id := c1_rec.key;
    pdate := c1_rec.pdate;    
    subtotal := to_number(c1_rec.pamount) + to_number(c1_rec.paint);

    /*dbms_output.put_line('key: '||key_id||' pdate: '||pdate||' gross: '|| null||
      ' memo: '||c1_rec.pmemo||' percent: '||null||' payto: '||c1_rec.payto||
      ' subtotal: '||subtotal);*/
    insert into pencash_calc
    values(key_id, pdate, null, c1_rec.pmemo, null, c1_rec.payto, subtotal);

    if pdate = temp_pdate and temp_key = key_id then
          gross_amt := gross_amt + subtotal;
    else
      update pencash_calc
      set grossamt = gross_amt
      where key_id = temp_key
      and pdate = temp_pdate;

          temp_pdate := pdate;
      temp_key := key_id;

      gross_amt := gross_amt + subtotal;
    end if;
    FETCH c1 into c1_rec;
  END LOOP;

   CLOSE c1;

   COMMIT;

   END;
/Start Free Trial