Home » RDBMS Server » Networking and Gateways » Invalid Number error from gateway (Oracle 10g, Sun Solaris 5.9, mainframe db2)
Invalid Number error from gateway [message #288497] Mon, 17 December 2007 15:22 Go to next message
yogeshse
Messages: 11
Registered: December 2005
Location: Chennai
Junior Member
Hi,

let me tell something about my environment before telling actual problem.
I'm using Oracle 10g (recently moved to from 9i) and pull data from Mainframe db using Oracle transparant gateway (9i version).

The cursor query which is failing given below. The proc basically reads 25 cust nmbrs in each iteration and populates the pl/sql array (remaining pl/sql array elements are populated to 0 when there are less than 25 cust in last iteration)from file and opens the cursor across the gateway and dumps the data into another flat file.
when this cursor query is executed first time, it's failing with the error -

ORA-01722: invalid number from ORACLE to a non-Oracle system returned this message:.
DSNT408I SQLCODE = -302, ERROR: THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER 2 IS INVALID OR TOO LARGE
FOR THE TARGET COLUMN OR THE TARGET VALUE
DSNT418I SQLSTATE = 22001 SQLSTATE RETURN
DSNT415I SQLERRP = DSNXRIHB SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -275 0 0 5 0 0
SQL DIAGNOSTIC INFORMATION ]
DSNT416I SQLERRD = X'FFFFFEED' X'00000000' X'00000000]
' X'00000005' X

The same query is running fine when it's re-ran with out doing any changes to either code or data.
Also, the query was working fine on Oracle 9i.

The cursor query is given below -
TYPE v_cust_nmbr_tbl_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;

  CURSOR alt_csr (p_cust_nmbr_tbl v_cust_nmbr_tbl_type, p_curr_date VARCHAR2) IS
    SELECT LPAD(TO_CHAR(cust_nmbr),11)      ||
           RPAD(NVL(alt_id_type_cd,' '),8)  ||
           RPAD(alt_id,20)                  ||
           RPAD(NVL(alt_id_vldtn_cd,' '),1) ||
           RPAD(eff_dt,10)                  ||
           RPAD(end_dt,10)                  ||
           RPAD(NVL(apprvd_state_cd,' '),2) ||
           RPAD(last_updt_dt,10)            ||
           RPAD(last_updt_tm,8)             ||
           '     ' field
      FROM sales.cust_alt_id@gt_sales CAI
     WHERE cust_nmbr        IN
(
p_cust_nmbr_tbl(1),
p_cust_nmbr_tbl(2),
.
.<25 values in the list here>
.
p_cust_nmbr_tbl(25)
)
       AND end_dt           > p_curr_date
       AND RTRIM(alt_id)      IS NOT NULL
       AND alt_id_vldtn_cd IN ('0','a','q','y')
       AND (alt_id_type_cd IN ('MN','OT','UN')
            OR
            (alt_id_type_cd = 'MN'
             AND
             SUBSTR(alt_id,1,1) != 'A')
            OR
            (alt_id_type_cd = 'LCN'
             AND
             apprvd_state_cd != '  '));

p_cur_date is varchar2(10); and is initialised as

p_cur_date:=TO_CHAR(SYSDATE,'yyyy-mm-dd');


What would be reason for it's failure when it's run first time??
Please advice.

Thanks in advance
Yogesh

[Mod-edit: Frank added code-tags]

[Updated on: Tue, 18 December 2007 00:09] by Moderator

Report message to a moderator

Re: Invalid Number error from gateway [message #288568 is a reply to message #288497] Tue, 18 December 2007 00:55 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are limits on Oracle datatypes (as for all other rdbms datatypes).
For instance, valid numbers are (-10E126,-10E-130),0,(10E-130,10E126).
Other RDBMS accept more values. For instance, MS/Access can store up to 10E256 (iirc).

Otherwise, you may have a compatibility problem between the gateway and versions at both side. Check compatibility.

Regards
Michel


Previous Topic: TNS Error for seconds occasionally through out day
Next Topic: Connection Manager Performance Issue
Goto Forum:
  


Current Time: Sat Nov 02 12:22:15 CDT 2024