Array Processing

From: Hicham Douba <hdouba_at_sprynet.com>
Date: Mon, 28 Sep 1998 16:08:39 GMT
Message-ID: <360fb3dd.8238989@news.sprynet.com>

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);

end;
/

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