摘要：BULK COLLECT读取含空日期字段的BUG
总述：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 &n, bsp;
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
