BULK COLLECT读取含空日期字段的BUG

关注

原创最新推荐文章于 2026-06-24 14:04:21 发布 1.8k 阅读 AI 写同款· GEO 优化 ›

CRM智能化失败的真相：仓库优先架构才是AI落地根基现代CRM系统本质是销售流程记录工具，其数据结构天然不支持AI所需的多维、时序、高保真特征输入。当企业试图在未统一数据源的前提下直接叠加AI功能，必然遭遇数据孤岛、特征失真与闭环断裂三大瓶颈。Warehouse-First Architecture（仓库优先架构）通过确立现代数据仓库为唯一可信数据源，实现CRM、行为、服务等全域客户数据的实时接入、语义建模与AI原生计算，将AI从‘概率输出’升级为‘可执行决策’。该范式已在B2B SaaS、电商、教育等行业验证：提升线索转化率23%、降低客户流失率22%，并阅读详情

总述：Oracle 9201中，在PL/SQL中使用BULK COLLECT读取含有空日期字段的表时会产生Ora-0331错误，并造成数据库连接断开。
数据库版本说明：

Oracle9i Enterprise Edition Release 92010 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 92010 - Production
Windows 2000 Version 50 Service Pack 4, CPU type 586

11:22:49 SQL> create table test
11:23:07   2 (id number11:23:07   3   entertime date11:23:07   4   state varchar2(1)11:23:07   5   state_date date)
11:23:07   6 partition by hash(id);

表已创建。

已用时间: 00: 00: 0000
11:23:07 SQL> insert into test values(seq_testnextval,null,0,sysdate);

已创建 1 行。

已用时间: 00: 00: 0000
11:23:21 SQL> /

已创建 1 行。

已用时间: 00: 00: 0000
11:23:23 SQL> /

已创建 1 行。

已用时间: 00: 00: 0000
11:23:24 SQL> /

已创建 1 行。

已用时间: 00: 00: 0000
11:23:24 SQL> insert into test values(seq_testnextval,sysdate,0,sysdate);

已创建 1 行。

已用时间: 00: 00: 0000
11:23:34 SQL> /

已创建 1 行。

已用时间: 00: 00: 0000
11:23:35 SQL> /

已创建 1 行。

已用时间: 00: 00: 0000
11:23:35 SQL> /

已创建 1 行。

已用时间: 00: 00: 0000
11:23:36 SQL> commit;

提交完成。

已用时间: 00: 00: 0000
11:23:39 SQL> select * from test;

        ID ENTERTIME S STATE_DATE
---------- ---------- - ----------
         9            0 18-5月 -05
        10            0 18-5月 -05
        11            0 18-5月 -05
        12            0 18-5月 -05
        13 18-5月 -05 0 18-5月 -05
        14 18-5月 -05 0 18-5月 -05
        15 18-5月 -05 0 18-5月 -05
        16 18-5月 -05 0 18-5月 -05

已选择8行。

已用时间: 00: 00: 0001
11:23:43 SQL> create or replace procedure p_test as
11:23:50   2    type tb_test is table of test%rowtype;
11:23:50   3    cursor cs_test is
11:23:50   4      select * from test;
11:23:50   5    ar_test tb_test;
11:23:50   6 begin
11:23:50   7    open cs_test;
11:23:50   8    loop
11:23:50   9      fetch cs_test bulk collect
11:23:50 10        into ar_test limit 5;
11:23:50 11      for i in 1 ar_testcount loop
11:23:50 12        dbms_outputput_line(ar_test(i)entertime);
11:23:50 13      end loop;
11:23:50 14      exit when cs_test%notfound;
11:23:50 15    end loop;
11:23:50 16    close cs_test;
11:23:50 17 end;
11:23:52 18 /

过程已创建。

已用时间: 00: 00: 0000
11:23:53 SQL> exec p_test;
BEGIN p_test; END;

*
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束
已用时间: 00: 00: 0002
11:24:01 SQL> spool off

以下来自Oracle支持网站，原始的Bug#2269035可能由于时间过久无法找到了。

Bug 号    2669115
已归档    14-NOV-2002    已更新    18-NOV-2002
产品    Oracle Server - Enterprise Edition    产品版本    92010
平台    Microsoft Windows (32-bit)    平台版本    无数据
数据库版本    92010    影响平台    Generic
优先级    Severe Loss of Service    状态    Closed, Duplicate Bug
基本 Bug    2269035    修复产品版本    无数据

问题陈述:

ORA-3114 OCCURS WHEN BULK COLLECT INTO USED TO STORE ROWS INTO A PLSQL TABLE

*** 11/14/02 06:15 am ***
Customer Tar # (Mandatory for Customer Bugs): 2724438999
Problem description:
-------------------
A procedure has been created with a cursor and the FETCH BULK COLLECT INTO
option based on the dept table
It works fine It fails if a date column is added into the table and change
the code of the procedure accordingly
Error: ORA-03114: not connected to ORACLE, occurs and sqlplus session
disconnected from the database
This happens only with 92010 database Since it doesn't occur with
92020 database on solaris, I am not
sure whether it has been fixed in this version This patch is not available
for Windows NT/2000
Keywords:
--------
stored procedure, bulk collect into, fetch, date column
Testcase location:
-----------------
Testcase step-by-step instructions:
----------------------------------
1) Connect to scott schema
2) Create a procedure using the following code:
create or replace Procedure dept_list is
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE index by pls_integer;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs limit 4;
for i in 14 loop
dbms_outputput_line ('looping i: ' || i);
dbms_outputput_line ('Dept Name: ' || dept_recs(i)dname);
end loop;
dbms_outputput_line ('closing cursor');
close c1;
END;
/
3) Execute the procedure
4) Add one more date column in dept table using:
alter table dept add (crdate date);
5) Re-create the proceduce with the following modified code:
create or replace Procedure dept_list is
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE index by pls_integer;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc, crdate FROM dept;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs limit 4;
for i in 14 loop
dbms_outputput_line ('looping i: ' || i);
dbms_outputput_line ('Dept Name: ' || dept_recs(i)dname);
end loop;
dbms_outputput_line ('closing cursor');
close c1;
END;
6) Execute the procedure It will fail with the error ORA-03114: not
connected to ORACLE
Generic/Port-specific findings:
----------------------------------
Did you test with the latest version? Yes
Rep? Platform Client Client Ver RDBMS Ver
---- ------------ --------- ------------- ------------
Y MS Windows 2000 80600 92010
Y MS Windows NT 80600 92010
N Sun SPARC Solaris 92020 92020
Available workarounds:
---------------------
None
Related bugs:
------------
Did you search for duplicate bugs?
Yes Bug:2202677 Abstract: ORA-3113 FROM BULK COLLECT INTO TABLE OF RECORDS
The bug says it has been fixed in 920 But it reproduces in 92010
Additional information:
----------------------
*** 11/14/02 06:29 am *** (CHG: Sta->36 G/P->G Asg->NEW OWNER)
*** 11/14/02 06:29 am ***
Testcase is specific to 92+ versions
Crashes on 9201 on Solaris - ie gives ORA-3113 In trace is:
ORA-07445: exception encountered: core dump [ldxeti()+20] [SIGSEGV]
[Address not mapped to object] [0x0] [] []
Works on 9202 and 10i (RDBMS_MAIN_SOLARIS_021108)
This is a duplicate of bug 2269035

收录于