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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms