Re: Fetching into arrays

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 6 Sep 2001 19:14:23 +0400
Message-ID: <9n83lb$7cs$1_at_babylon.agtel.net>

"Andreas Krämer Flecken" <a.kraemer-flecken_at_fz-juelich.de> wrote in message news:3B89E6BC.3009D269_at_fz-juelich.de...
> Hi to everybody,
>
> I have a problem with fetching data from tables into an array within my
> PL/SQL code. My data are of number type and I like to write a general
> procedure which allows to fetch one complete row from different tables.
> In principle the use of %ROWTYPE will do it, but only for one table.
> However not for different tables. The number of rows in my tables is
> different but the data type always the same. A fetch command into an
> array gives already an error during compilation that fetching data into
> an array is not allowed.
>
> Does somebody know a method to fetch data in an array?
>
> Andreas
>
> --
> -------------------------------------------------------------------------
>
> *A.Krämer-Flecken Tel.: +49 2461 614521
> *Institut für Plasmaphysik FAX : +49 2461 615452
> *Forschungszentrum Jülich GmbH email:a.kraemer-flecken_at_fz-juelich.de
> *52425 Jülich flecken_at_eifel-net.net
> *GERMANY
> -------------------------------------------------------------------------
>
>
>

This is a good case for DBMS_SQL. Here's an example (almost identical to examples on DBMS_SQL in Oracle docs):

• dbms_sql.Number_Table is table of number index by binary_integer;

create or replace
function getColumnAsNumArray(table_name varchar2,

                             column_name varchar2,
                             query_trailer varchar2) return dbms_sql.Number_Table

is
  rv dbms_sql.Number_Table;
  c Number;
  r Number;
begin

    c := dbms_sql.open_cursor;
    dbms_sql.parse(c,

                   'select '||column_name||' from '||table_name||' '||query_trailer,
                   dbms_sql.native);

    dbms_sql.define_array(c, 1, rv, 10, 1);     r := dbms_sql.execute(c);
    loop

      r := dbms_sql.fetch_rows(c);
      dbms_sql.column_value(c, 1, rv);
      exit when r != 10;

    end loop;
    dbms_sql.close_cursor(c);
    return rv;
exception

    when others then

        if dbms_sql.is_open(c) then
          dbms_sql.close_cursor(c);
        end if;
     raise;

end;

This function will fetch all data from specified table_name.column_name. query_trailer may specify where condition, order by and group by clauses.

for example

numarray := getColumnAsNumArray('a_table','number_column','where number_column > 0 order by number_column desc');

will populate numarray with result of the following query:

select number_column from a_table where number_column > 0 order by number_column desc

Refer to DBMS_SQL package documentation for all the cool features this package provides. Regretably, native dynamic sql doesn't have ability to fetch into arrays (as of 8i, I didn't check what's new in 9i regarding native dynamic sql). Received on Thu Sep 06 2001 - 17:14:23 CEST