Jerry N
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?
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?
you wouldn't use associative arrays for an IN clause, but rather a nested table type.
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 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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks once more!
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?