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
Registered: December 2005
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(NVL(alt_id_vldtn_cd,' '),1) ||
RPAD(NVL(apprvd_state_cd,' '),2) ||
' ' field
FROM sales.cust_alt_id@gt_sales CAI
WHERE cust_nmbr IN
.<25 values in the list here>
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')
(alt_id_type_cd = 'MN'
SUBSTR(alt_id,1,1) != 'A')
(alt_id_type_cd = 'LCN'
apprvd_state_cd != ' '));
p_cur_date is varchar2(10); and is initialised as
What would be reason for it's failure when it's run first time??
Thanks in advance
[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
Registered: March 2007
Location: Nanterre, France, http://...
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.
Current Time: Thu May 25 13:20:53 CDT 2017
Total time taken to generate the page: 0.16310 seconds