Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Array Processing
Howdie,
I have a weird problem and I don't know if I it can be solved by changing a parameter in INIT.ORA or it's an ORACLE bug! Consulting Oracle might give us a clue.
To be specific, I'm doing an array fetch. If I run it to fetch 100 rows at a time, no error is generated. If I run it for 500 rows at a time, I get the following error:
ORA-03113: end-of-file on communication channel
The code I'm trying to run follows:
set serveroutput on
declare
cur pls_integer := dbms_sql.open_cursor;
d_table dbms_sql.date_table;
n_table dbms_sql.number_table;
vc_table dbms_sql.varchar2_table;
fdbk pls_integer;
rows_fetched pls_integer;
timing pls_integer;
d date;
n number;
vc varchar2(40);
rows_to_fetch pls_integer :=500;
begin
timing := dbms_utility.get_time;
dbms_output.enable(99999);
dbms_output.put_line('time = ' || to_char(dbms_utility.get_time));
dbms_sql.parse(cur, 'select n, vc, d from t', dbms_sql.native);
dbms_sql.define_array(cur, 1, n_table, rows_to_fetch, 1); dbms_sql.define_array(cur, 2, vc_table, rows_to_fetch, 1); dbms_sql.define_array(cur, 3, d_table, rows_to_fetch, 1);
fdbk := dbms_sql.execute(cur);
rows_fetched := dbms_sql.execute_and_fetch(cur); dbms_output.put_line('rows_fetched= ' || to_char(rows_fetched));
dbms_sql.column_value(cur, 1, n_table); dbms_sql.column_value(cur, 2, vc_table); dbms_sql.column_value(cur, 3, d_table);
for i in 1 .. rows_fetched loop
d := d_table(i);
n := n_table(i);
vc := vc_table(i);
end loop;
dbms_sql.close_cursor(cur);
dbms_output.put_line('time = ' || to_char(dbms_utility.get_time)); dbms_output.put_line('time = ' || to_char(dbms_utility.get_time - timing));
exception
when others then
dbms_output.put_line(sqlerrm); dbms_output.put_line(sqlcode); dbms_sql.close_cursor(cur);
You can change assign 100 to rows_to_fetch to make it run for 100 rows. Moreover, I you run it for 300 rows, you get a different error message:
ORA-01403: no data found
100
This is a result of fetching values from the n_table that don't exist. Such values should exist.
The table, I'm fetching from, has the following structure:
SQL> desc t
Name Null? Type ------------------------------- -------- ---- N NUMBER VC NOT NULL VARCHAR2(30) D DATE
There are over 5000 rows in it.
Your help is appreciated.
Kind regards,
Hicham Douba (613)829-3004 Received on Mon Sep 28 1998 - 11:08:39 CDT