【请教】ora-24345: A Truncation or null fetch error occurred

hencoolhennb

刘大及各位XDJM，请教下，谢谢！

数据库版本：ORACLE 11G RAC, 11.2.0.3
应用程序语音：C++
问题：
   一个生产上的select语句，查询的表大概有1700万行数据。select语句在sqlplus中执行，可以正常返回数据，应用程序中执行时，报错：ora-24345 A Truncation or null fetch error occurred。

1. 我通过oerr ora 24345查询error信息
24345, 00000, "A Truncation or null fetch error occurred"
// *Cause:  A truncation or a null fetch error"
// *Action: Please ensure that the buffer size is long enough to
//          store the returned data.
意思应该是应用程序的变量长度比数据的实际长度小。

2. alter日志没有任何相关错误及警告

3. SQL语句如下：
SELECT log_seq,
       trade_code,
       account,
       amount,
       trade_date,
       ext_seq,
       channel_code,
       proc_code,
       cardtype,
       pubflag,
       rsp_code,
       order_code,
       pay_seq,
       account_attr,
       start_date,
       end_date,
       service
  FROM t_pgw_deallog
WHERE proc_code = '86740001'
   AND account_attr = '867400'
   AND channel_code = '10000001'
   AND pubflag = '1'
   AND service IN ('PGW1201', 'PGW1202')
   AND to_char(start_date, 'YYYYMMDD') >= to_char(SYSDATE - 1, 'YYYYMMDD')
   AND to_char(start_date, 'YYYYMMDD') <= to_char(SYSDATE, 'YYYYMMDD')
   AND rsp_code = '0040';

请问是否有遇到过？或者有什么思路？感谢！

Liu Maclean(刘相兵

贴出 t_pgw_deallog表的DDL

Liu Maclean(刘相兵

ODM FINDING:
Reading Large BLOB Data Using OCI API Fails With ORA-24345 (Doc ID 1418323.1)
Applies to:
Oracle Server - Enterprise Edition - Version: 8.0.6.0 and later   [Release: 8.0.6 and later ]
Information in this document applies to any platform.
Symptoms
When using OCIStmtGetPieceInfo/OCIStmtSetPieceInfo, writing a blob value of approximately 5 billion bytes works fine.  But, when reading the BLOB value it stops at about 2147548723 (this varies with the piece size but its always close to this) with the following error:

ORA-24345: A Truncation or null fetch error occurred

Changes
Reading BLOB Using OCI.
Cause

This is expected behaviour since

    OCIDefineByPos()
    OCIDefineByName()  

uses for maximum size type

sb4  (signed byte - size 4 = 32bit )
which limits maximum size to

INT_MAX  =  2147483647
Solution

Instead of using;

    OCIBindByPos()
    OCIBindByName()
    OCIDefineByPos()
    OCIDefineByName()

for binding / defining BLOB columns, use the OCI Lob routines for LOBs greater than 2G.

For lobs greater than 4G you have to use;

OCILobRead2()

which allows access to LOBs from 0 to maximum size (Depends on Database Release)
Note: OCILobRead2() was introduced in Oracle 10g Release 10.1.0.2.

hencoolhennb

Liu Maclean(刘相兵 发表于 2013-12-14 16:16
贴出 t_pgw_deallog表的DDL

--GETDDL获取的
CREATE TABLE "BPPF_PGW"."T_PGW_DEALLOG"
   (    "LOG_SEQ" VARCHAR2(16) NOT NULL ENABLE,
        "MTI" CHAR(4),
        "TRADE_CODE" CHAR(6),
        "ACCOUNT" VARCHAR2(32),
        "AMOUNT" NUMBER(12,2),
        "TRADE_DATE" CHAR(8),
        "TRADE_TIME" CHAR(6),
        "EXT_SEQ" VARCHAR2(32),
        "CHANNEL_CODE" VARCHAR2(8),
        "MERCH_CODE" VARCHAR2(20),
        "PROC_CODE" VARCHAR2(8),
        "CARDTYPE" NUMBER(4,0),
        "PUBFLAG" NUMBER(2,0),
        "RSP_CODE" VARCHAR2(4),
        "SUMMARY" VARCHAR2(64),
        "ORDER_CODE" VARCHAR2(24),
        "PAY_SEQ" VARCHAR2(40),
        "SRC_EXT_SEQ" VARCHAR2(32),
        "REVERSAL" NUMBER(2,0) DEFAULT 0,
        "ACCOUNT_ATTR" VARCHAR2(6),
        "EVENT_SEQ" VARCHAR2(32),
        "BANK_RSP_CODE" VARCHAR2(24),
        "BANK_RSP_DESC" VARCHAR2(256),
        "START_DATE" DATE,
        "END_DATE" DATE,
        "SEND_DATE" DATE,
        "RECV_DATE" DATE,
        "SERVICE" CHAR(7),
        "IP" VARCHAR2(20),
        "ACTION_CODE" VARCHAR2(128),
        "AREA_CODE" VARCHAR2(6),
        "ACCOUNT_NAME" VARCHAR2(128),
        "CUSTOM" VARCHAR2(512),
        "RESERVE1" VARCHAR2(512),
        "RESERVE2" VARCHAR2(512),
        "RESERVE3" VARCHAR2(512),
         CONSTRAINT "PK_PGW_DEALLOG" PRIMARY KEY ("LOG_SEQ")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BPPF_PGW_IDX_A01"  ENABLE,
         CONSTRAINT "UQ_PGW_DEALLOG_1" UNIQUE ("CHANNEL_CODE", "EXT_SEQ")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BPPF_PGW_IDX_A01"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BPPF_PGW_DAT_A01"

hencoolhennb

Liu Maclean(刘相兵 发表于 2013-12-14 16:16
ODM FINDING:
Reading Large BLOB Data Using OCI API Fails With ORA-24345 (Doc ID 1418323.1)
Applies t ...

谢谢刘大
这个应该是指表字段含有BLOB类型或LOB类型时，OCI的函数使用错误会出现这个问题

xteitxu

关注中。。。。

hencoolhennb

感谢大家的关注和帮助。
最后定位，由于开发人员为了SQLPLUS查询时间方便，修改了应用服务器的环境变量（系统级），
NLS_DATE_FORMAT 时间格式，造成返回时间字段的数据长度发生变化，OCI函数报异常OCI_SUCCESS_WITH_INFO。
修改了环境变量的语句，位于.profile中
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'。
取消环境变量后，则异常消失。