从 GATEWAY 中取数报：ORA-600 [HO define: Long fetch]

郑全

ORA-600 [HO define: Long fetch] Error Message When Selecting Data Via Oracle Database Gateways (Doc ID 1224783.1)

n this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Database Gateway for SQL Server - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]

Information in this document applies to any platform.

SYMPTOMS

The following error message is received when making a select using an Oracle Database Gateway (such as Dg4ODBC, DG4MSQL) with third party tools such as PL/SQL Developer or Toad for Oracle  -

select * from table_name@dblink ;

ORA-00600. Internal Error Code, Arguments: [HO define: Long fetch], [], [], [], [], [], [], []

The problem can also happen when using Oracle software.

This can happen with Gateway versions from 11.1 to 12.1.

CAUSE

The ORA-600 can happen for several reasons, the root cause of the error with the third party applications depends on the table being selected.

1. The table description contains 2 long or long raw columns , for example -

CURRENCEYDATA              LONG RAW

MEMO                             LONG

2. The non-Oracle table has a column defined as var(max).

In Oracle there are limitations with LONG columns:

A table can contain only one LONG column.

See  Note 463115.1 Restrictions on LONG Datatype

SOLUTION

The root cause of the ORA-600 error with the third party applications depends on the table being selected and certain applications will cause an ORA-600.

The workround is -

1. Create a view on the SQL Server or Oracle side avoiding a select of more than one long/long raw column at the same time.

2. Specify the select list  to make sure only one long or long raw column is fetched at the same time.

3. Create a view on the non-Oracle database that truncates columns to 4000 characters or less.

For example on SQL*Server -

CREATE VIEW view_log_bis AS SELECT

( ID,

LogTime,

Action,

InstID,

Username,

Error,

SUBSTRING (Parm1,1 ,4000) as Parm1_4000 ,

SUBSTRING (Parm1,4001,8000)as Parm1_8000,

SUBSTRING (Parm1,8001,12000) as Parm1_12000,

SUBSTRING (Parm1,12001,16000) as Parm1_16000,

Cert

FROM view_log;

4. Use a view on SQL Server to cast the var(max) or nvarchar(max)columns to another data type.

If the a column is nvachar(max) then you will need to use substring and convert to split the column into smaller chunks.

For example -

create view BUNSEKIKEKKA_VW as

select CONTENTID,

CONVERT(VARCHAR(4000), SUBSTRING (NOTESDBMEISHOU,1 ,4000)) as NOTESDBMEISHOU_1,

SUBSTRING(NOTESDBMEISHOU,1 ,30) as NOTESDBMEISHOU_2,

CONVERT(NVARCHAR(2000), SUBSTRING (NOTESDBMEISHOU,1 ,2000)) as NOTESDBMEISHOU_3

from BUNSEKIKEKKA;

The column NOTESDBMEISHOU is defined in SQL*Server as nvarchar(max).

Describing it now from Oracle shows:

SQL> desc BUNSEKIKEKKA_VW@DG4MSQL_DB;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

CONTENTID                                 NOT NULL NVARCHAR2(450)

NOTESDBMEISHOU_1                                   VARCHAR2(4000 CHAR)

NOTESDBMEISHOU_2                                   LONG

NOTESDBMEISHOU_3                                   NVARCHAR2(2000)

5. Use Oracle tools instead of third-party tools.