Solved

PL/SQL: Two dimensional associative array sytax

Posted on 2013-11-20
7
2,152 Views
Last Modified: 2013-11-25
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
Comment
Question by:GNOVAK
  • 3
  • 2
  • 2
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39663953
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39665615
you wouldn't use associative arrays for an IN clause, but rather a nested table type.
0
 

Author Comment

by:GNOVAK
ID: 39665635
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39665725
>>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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 39665747
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39665766
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
 

Author Closing Comment

by:GNOVAK
ID: 39675087
Thanks once more!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now