Re: Multi-dimensional arrays

From: malcolm <malcolmarnold_at_gmail.com>
Date: Fri, 09 Nov 2007 10:22:59 -0000
Message-ID: <1194603779.252170.248620@o80g2000hse.googlegroups.com>

On Nov 8, 9:25 pm, m..._at_mtekusa.com wrote:
> On Nov 8, 3:19 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > m..._at_mtekusa.com wrote:
> > > On Nov 8, 12:07 pm, DA Morgan <damor..._at_psoug.org> wrote:
> > >> m..._at_mtekusa.com wrote:
> > >>> Hi Everyone,
> > >>> We are running Oracle 8.1.7. I'm trying to create a two dimensional
> > >>> array. I realize that this is almost impossible prior to 9i. But,
> > >>> this is all I have to work with right now.
> > >>> Can someone give me an example on how to set this up in a package?
> > >>> I'm lost and have tried a number of different things.
> > >>> I'm hoping for something somewhat simple, that will not take 30 lines
> > >>> of code to implement and use.
> > >>> Thank you for your help.
> > >>> John
> > >> It is also not possible with 11g.
>
> > >> You don't need a two dimensional array to solve the problem as you have
> > >> already been told.
> > >> --
> > >> Daniel A. Morgan
> > >> University of Washington
> > >> damor..._at_x.washington.edu (replace x with u to respond)
> > >> Puget Sound Oracle Users Groupwww.psoug.org
>
> > > Daniel,
>
> > > I will be returning X number of rows with Y number of columns for each
> > > of those rows. To me, that looks like a 2 dimensional array.....
>
> > > John.
>
> > One interpretation is a two dimensional array. Another interpretation
> > is two single dimensional arrays. Oracle gives you one of them and
> > not the other.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> Yeah, the 2 one dimensional arrays is one alternative I guess. But I
> know our PHP guys will not like it, since they have to parse it. The
> arrays will have to be keyed somehow so I know what row in Array #1
> goes with what row in Array #2, unless they are in the same order and
> have the same indexes, etc.........
>
> John

Hi John, Daniel,

I'm not really sure I understand what you're talking about here, sorry. While you can't have two-dimensional arrays, you can have an array of arrays (better called a nested table of nested tables).

You could do something like this, and pass back to PHP an 'array' of columns, where each column is an 'array':

declare

	type table_of_tables is table of dbms_sql.varchar2_table;
	d dbms_sql.desc_tab;
	num_columns integer;
	temp dbms_sql.varchar2_table;
	all_results table_of_tables;
	c integer;
	i integer;
begin
	c := dbms_sql.open_cursor;
	dbms_sql.parse(c, 'select rownum, sysdate, ''hello'' from
user_objects', dbms_sql.native);
	dbms_sql.describe_columns(c, num_columns, d);

	for i in 1 .. num_columns loop
		 dbms_sql.define_array(c, i, temp, 1000, 1);
	end loop;

	i := dbms_sql.execute(c);

	i := dbms_sql.fetch_rows(c);
	all_results := table_of_tables();
	all_results.extend(num_columns);

	for i in 1 .. num_columns loop
		 dbms_sql.column_value(c, i, temp);
		 all_results(i) := temp;
	end loop;

	dbms_sql.close_cursor(c);

	for c in 1 .. all_results(1).count loop
		for i in 1 .. num_columns loop
			dbms_output.put(all_results(i)(c) || ' ');
		end loop;
		dbms_output.put_line('');
	end loop;

end;
/ Received on Fri Nov 09 2007 - 04:22:59 CST