Link to home
Start Free TrialLog in
Avatar of Jerry N
Jerry NFlag for United States of America

asked on

PL/SQL: Two dimensional associative array sytax

I need to take a Varchar2 and a date from MyTable and place it into a two dimensional array in Oracle.
I know I can simulate this somehow with two associative arrays, but I dont know how I would
 set it up or Insert from a table into it.

Also, once it is set up can I use it with an select * from myTable where mychar IN(Array) statement?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'm not following.

You want an array of varchar2,date?

Create a record or object with those two columns then use an array of that record.

>>once it is set up can I use

No but you should be able to cast it as a table and select from it.

Also it would not be:
myTable where mychar IN(Array)

But you might be able to get away with:
myTable where (mychar, mydate) IN( select arrayChar, arrayDate from table(Array))

Can you provide some sample data and expected results?
you wouldn't use associative arrays for an IN clause, but rather a nested table type.
Avatar of Jerry N

ASKER

I've heard that an associative array of an array is used for a multidimensional array. I want to understand that process.
I dont understand how I could populate and use it. All the docs I've read fall short.

For example, I wish to store a varchar2 and a date. If I create an array :
DECLARE
TYPE myArray IS TABLE OF varchar2
 INDEX BY VARCHAR2(10);
myvar varchar2;
<create second array(MyArray2)  here?>
BEGIN
  Insert myfield, mydate into myArray2 from AnotherTable
...

Then I would like to do something like mentioned above with another table:
MyTable2 where (myCharField, myDateField) IN (select ?? from myArray2)



...
>>I wish to store a varchar2 and a date. If I create an array :

I'm still not understanding how you want to use both of these in a multi-dimensional array.

Can you post from sample data showing what you want this to look like?
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jerry N

ASKER

Thanks once more!