Re: Multi-dimensional arrays

On Nov 9, 5:47 pm, malcolm <malcolmarn..._at_gmail.com> wrote:
> On Nov 9, 1:37 pm, m..._at_mtekusa.com wrote:
>
>
>
> > On Nov 9, 4:22 am, malcolm <malcolmarn..._at_gmail.com> wrote:
>
> > > 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;
> > > /
>
> > Malcom,
>
> > Thanks for your code. But when I try it, I get this error:
>
> > ERROR at line 2:
> > ORA-06550: line 2, column 33:
> > PLS-00507: a PLSQL Table may not contain a table or a record with
> > composite fields
> > ORA-06550: line 2, column 9:
> > PL/SQL: Item ignored
>
> > Perhaps I'll just shelf this project until we go 10g........
>
> > John
>
> Oops - sorry about that. I didn't have an 8i database around to test
> it on, but thought it would work. Forget just how limited 8i was...

Me too. 9i PL/SQL reference, "What's new": <http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/ whatsnew.htm#969821>
"You can nest the collection types, for example to create a VARRAY of PL/SQL tables, a VARRAY of VARRAYs, or a PL/SQL table of PL/SQL tables. You can model complex data structures such as multidimensional arrays in a natural way."
Apologies.

There might be a way to fake it using functions to return collections, but it will almost certainly break your 30-line rule. Received on Sun Nov 11 2007 - 11:04:40 CST