My Oracle Support Banner

Calling a Procedure That Uses a Gateway Connection Gives Error ORA-01722 Invalid Number but Second Run is Successful (Doc ID 1476826.1)

Last updated on JANUARY 09, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database Gateway for DRDA - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database Gateway for Informix - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database Gateway for SQL Server - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database Gateway for Sybase - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

A procedure using a Gateway to access a non-Oracle database runs successfully from a 9i RDBMS.
After upgrading the RDBMS and the Gateway to a 10g or 11g version the procedure fails on the first or following executions with errors -

 

For example -

 

CREATE OR REPLACE PROCEDURE  extract_tags IS

lr_arc  arc_rec@GATEWAY_LINK%rowtype;  

num_rows             INTEGER;

--Cursor simply stores 3 numbers, these numbers will be looped  and used in where conditon for data retrival
cursor cur_extract_tags is
    select 26693703 tag_mst_ctl_no from dual
    union
    select 26633312 from dual
    union
    select 25597906 from dual;

BEGIN
  FOR rec_extract_tags IN cur_extract_tags     LOOP
    lr_arc := NULL;

   select
        "arc_item","arc_brh","arc_frm","arc_grd",
        INTO lr_arc FROM arc_rec@GATEWAY_LINK WHERE "arc_item" = rec_extract_tags.tag_mst_ctl_no;

INSERT INTO arc ( SELECT *  FROM arc_rec@GATEWAY_LINK WHERE "arc_item" = rec_extract_tags.tag_mst_ctl_no);

SELECT *  INTO lr_arc FROM arc_rec@GATEWAY_LINK WHERE "arc_item" = rec_extract_tags.tag_mst_ctl_no;

END LOOP;
End extract_tags;
/

SQL> BEGIN  extract_tags; END;
    2  *
   3  ERROR at line 1:
   4  ORA-01722: invalid number
   5  ORA-06512: at "OWNER.EXTRACT_TAGS", line xx
   6  ORA-06512: at line 1
   7
   8
   9  SQL> BEGIN  extract_tags; END;
  10    2  /
  11
  12  PL/SQL procedure successfully completed.
  13
  14  SQL>

 

The first run fails but a subsequent attempt is successful.

Changes

 Upgrade of the RDBMS and Gateway to 10g or 11g.

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.