Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3681
  • Last Modified:

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?
0
GNOVAK
Asked:
GNOVAK
  • 3
  • 2
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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?
0
 
sdstuberCommented:
you wouldn't use associative arrays for an IN clause, but rather a nested table type.
0
 
GNOVAKAuthor Commented:
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)



...
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
slightwv (䄆 Netminder) Commented:
>>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?
0
 
sdstuberCommented:
This shows how to build an associative array of records (I think this is what you want based on your description, not really a 2-dimensional array).
Note, there is some inefficiency here because we can't do a bulk collect into the array.  Because you're trying to index by a string we have to assign each element manually.  The for-loop construction will do some bulk processing for us though (assuming 10gr2 or higher)  so this shouldn't be a big deal.

You can NOT use this array to do "IN" list filtering as you have shown above.  You will need to use a nested table collection instead.
Similar, but not the same thing.

DECLARE
    TYPE myrecord IS RECORD
    (
        mytext VARCHAR2(30),
        mydate DATE
    );

    TYPE myarray IS TABLE OF myrecord
        INDEX BY VARCHAR2(30);

    myvar myarray;
BEGIN
    FOR t IN (SELECT table_name, tablespace_name, last_analyzed FROM user_tables)
    LOOP
        myvar(t.table_name).mytext := t.table_name;
        myvar(t.table_name).mydate := t.last_analyzed;
    END LOOP;
END;

Open in new window

0
 
sdstuberCommented:
This shows how to use a nested table type within a sql IN clause.
Note  - because the collection type is used in a SQL statement, the collection type must be declared as a sql object.

In this example I'm using a collection that should already exist (with public privileges) on your database.


DECLARE
    myvar ora_mining_varchar2_nt; -- this is a SYS owned public type
-- declared as SYS.ora_mining_varchar2_nt AS TABLE OF VARCHAR2(4000)
BEGIN
      SELECT table_name
        BULK COLLECT INTO myvar
        FROM user_tables
    ORDER BY table_name;

    FOR i IN myvar.FIRST .. myvar.LAST
    LOOP
        DBMS_OUTPUT.put_line('your schema: ' || USER || ' owns a table named: ' || myvar(i));
    END LOOP;

    -- now find tables in other schemas that have the same name as tables I own.

    FOR t IN (  SELECT owner, table_name
                  FROM all_tables
                 WHERE owner != USER AND table_name IN (SELECT COLUMN_VALUE FROM TABLE(myvar))
              ORDER BY owner, table_name)
    LOOP
        DBMS_OUTPUT.put_line('schema: ' || t.owner || ' also owns a table named: ' || t.table_name);
    END LOOP;
END;

Open in new window

0
 
GNOVAKAuthor Commented:
Thanks once more!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now