English

Error: Underlying DBMS error[ORA-03120: two-task conversion routine: integer overflow]

Error Message

When using Oracle and ArcSDE, executing an SQL statement that contains multiple bind values that share the same binding name can result in the following error:

"Underlying DBMS error[ORA-03120: two-task conversion routine: integer overflow]".

Cause

An internal Oracle error is exposed when the Oracle client application binds literal values by name and the bind value is specified in multiple locations in the SQL statement.

A common example where ArcGIS binds by name in multiple locations is in a versioned query.

Code:
SELECT /*+ USE_NL(V__2210) INDEX(SHAPE F774_UK1) */ V__2210.eminx,
V__2210.eminy,V__2210.emaxx,V__2210.emaxy ,SHAPE.fid,SHAPE.numofpts,
SHAPE.entity,SHAPE.points,SHAPE.rowid
FROM
(SELECT /*+ LEADING */ b.SHAPE ,S_.sp_fid,S_.eminx,S_.eminy,S_.emaxx,
S_.emaxy FROM (SELECT /*+ INDEX(SP_ S774_IX1) */ DISTINCT sp_fid, eminx,
eminy, emaxx, emaxy FROM TOMB.S774 SP_ WHERE SP_.gx >= :1 AND SP_.gx <=
:2 AND SP_.gy >= :3 AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <=
:6 AND SP_.emaxx >= :7 AND SP_.emaxy >= :8)S_,
TOMB.MSD_Network_Junctions b WHERE S_.SP_FID = b.SHAPE AND
b.OBJECTID NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID FROM
TOMB.D2210 WHERE DELETED_AT IN (SELECT l.lineage_id FROM
SDE.state_lineages l WHERE l.lineage_name = :source_lineage_name AND
l.lineage_id <= :source_state_id) AND SDE_STATE_ID = 0) UNION ALL SELECT
/*+ LEADING */ a.SHAPE ,S_.sp_fid,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy
FROM (SELECT /*+ INDEX(SP_ S774_IX1) */ DISTINCT sp_fid, eminx, eminy,
emaxx, emaxy FROM TOMB.S774 SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND
SP_.gy >= :3 AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND
SP_.emaxx >= :7 AND SP_.emaxy >= :8)S_, TOMB.A2210 a,SDE.state_lineages SL
WHERE S_.SP_FID = a.SHAPE AND (a.OBJECTID, a.SDE_STATE_ID) NOT IN (SELECT
/*+ HASH_AJ */ SDE_DELETES_ROW_ID,SDE_STATE_ID FROM UTARC.D2210 WHERE
DELETED_AT IN (SELECT l.lineage_id FROM SDE.state_lineages l WHERE
l.lineage_name = :source_lineage_name AND l.lineage_id <= :source_state_id)
AND SDE_STATE_ID > 0) AND a.SDE_STATE_ID = SL.lineage_id AND
SL.lineage_name = :source_lineage_name AND SL.lineage_id <=
:source_state_id ) V__2210, TOMB.F774 SHAPE WHERE SHAPE.fid =
V__2210.sp_fid

In the above statement, the :source_lineage_name and :source_state_id bind values are referenced in multiple locations.

Once the application binds each literal value with a distinct binding (:source_lineage_name1, :source_lineage_name2, etc.), the error is no longer encountered.

Solution or Workaround

Convert the table's attributes from using Oracle's LONG RAW data type to BLOB data type (testing by ESRI indicates the problem is no longer reproducible), or contact ESRI Technical Support for further assistance.