Re: ORA-01457

From: VictorReinhart <victora.reinhart_at_phs.com>
Date: 23 Oct 2002 07:58:34 -0700
Message-ID: <af2a1855.0210230658.1d000276@posting.google.com>

> What is the data type of the column fetched?

  NUMBER(9,2)
> I would try increasing the number of decimals as it seems the precision is
> the problem. Try a PIC S9(11)V9999 COMP-3 or something like that instead.

Thanks for the suggestion, but the program works fine in Oracle 7.3. I think the variable is properly sized already.

Here is the TAR I opened w/Oracle:

Problem: ORA-1457 error at runtime of a COBOL program compiled with Oracle 8.1.7 precompiler.

I have tried various workarounds, but none work.

The following works fine with the Oracle 7.3 precompiler. The COBOL program runs just fine too. (We are fetching in batches). 10 SERVICE-CHARGE-AMT PIC S9(9)V99 COMP-3 OCCURS 700 TIMES. In the database, a describe returns:

 SERVICE_CHARGE_AMOUNT                    NUMBER(9,2)

The same code precompiles OK in the Oracle 8.1.7 precompiler, but at runtime, Oracle fails with:
SQLCODE = -1,457
SQLERRM : 8ORA-01457: converting column overflows decimal datatype

1. I try to work around the problem by changing the variable from COMP-3 to COMP as follows: 10 SERVICE-CHARGE-AMT PIC S9(9)V99 COMP OCCURS 700 TIMES.

Then, the precompiler fails with:
Error at line 814, column 21 in file hedb902.pco 00288 :SERVICE-CHARGE-AMT :WS-NULL-SCA,

....................1

PCB-S-00222, Size for host variable SERVICE-CHARGE-AMT exceeds maximum size

2) I reduce the size of the variable from 9 to 7 as follows: 10 SERVICE-CHARGE-AMT PIC S9(7)V99 COMP OCCURS 700 TIMES. Then, the precompiler fails with:
Error at line 814, column 21 in file hedb902.pco 00288 :SERVICE-CHARGE-AMT :WS-NULL-SCA,

....................1

PCB-S-00209, Scale is not supported for host variable "SERVICE-CHARGE-AMT" 3) I change the data type from COMP to COMP-5 as follows:  10 SERVICE-CHARGE-AMT PIC S9(7)V99 COMP-5 OCCURS 700 TIMES. Then, the precompiler fails with:
Error at line 814, column 21 in file hedb902.pco 00288 :SERVICE-CHARGE-AMT :WS-NULL-SCA,

....................1

PCB-S-00209, Scale is not supported for host variable "SERVICE-CHARGE-AMT" VictorReinhart Received on Wed Oct 23 2002 - 09:58:34 CDT