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

;                                                       

已选择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
---- ---------