Joining associative array with a table

[dbalearner]

I am new to associative arrays.

Here I am trying to do the equivalent of the folowing:


create table mytable as select n1,padding from T1 where rownum <=5;
Table created.


And join this table with T1 and the get the first 10 matching records

1 SELECT * FROM
2 (
3 SELECT
4 T1.n1
5 , T1.small_vc
6 , mytable.padding
7 FROM
8 mytable
9 , T1
10 WHERE
11 mytable.n1 = T1.n1
12 )
13* WHERE ROWNUM <= 10;



Now with assicative arrays I do

1 DECLARE
2 type array is table of T1%rowtype index by binary_integer;
3 l_data array;
4 l_rec T1%rowtype;
5 BEGIN
6 SELECT
7 *
8 BULK COLLECT INTO
9 l_data
10 FROM T1
11 WHERE ROWNUM <=5; -- the top 5 records
12 FOR i IN 1..l_data.count
13 LOOP
14 BEGIN
15 --
16 SELECT
17 *
18 INTO
19 l_rec
20 FROM
21 T1
22 WHERE
23 T1.n1 = l_data(i).n1;
24 EXCEPTION
25 WHEN OTHERS THEN
26 DBMS_OUTPUT.PUT_LINE(to_char(SQLCODE) || ' - ' || substr(SQLERRM,1,200));
27 END;
28 END LOOP;
29* END;
/
-1422 - ORA-01422: exact fetch returns more than requested number of rows
-1422 - ORA-01422: exact fetch returns more than requested number of rows
-1422 - ORA-01422: exact fetch returns more than requested number of rows
-1422 - ORA-01422: exact fetch returns more than requested number of rows
-1422 - ORA-01422: exact fetch returns more than requested number of rows



Obviously the coding is not right. Also I want to display the first 10 result set.

Thanks,

Learner

 

[carp]

It appears that you have more than one record per T1.n1 value. You have two ways to go from here - either figure out how to assure only one row will be returned or use a second array table to select into. Without knowing what your are really trying to do, it is a little difficult to assist you beyond this point.

 

[dbalearner]

Thanks Carp. At the end I decided to use pipelined table function as follows:

DROP FUNCTION array;
DROP FUNCTION regular_array;
DROP TYPE array_table_type;
DROP TYPE array_row_type;
--
--First define a row type that contains the columns we need
--
CREATE TYPE array_row_type
AS OBJECT
(
        n1       NUMBER(5),
        padding   VARCHAR2(200)
);
/
-- Create a table type
--
CREATE TYPE array_table_type AS TABLE OF array_row_type;
/
/*
   Now define a table function which returns a collection containing a subset of the columns from T1 table. Table function mimics the table and can be queried later
   using the TABLE function in from clause
*/
CREATE OR REPLACE FUNCTION array
(
        p_n1            IN      NUMBER          DEFAULT 0,
        p_padding       IN      VARCHAR2        DEFAULT '%'
)
RETURN array_table_type PIPELINED -- Pipelining negates the need to build huge collections by piping rows out of the function as they are created,
                                   -- saving memory and allowing subsequent processing to start before all the rows are generated
AS
BEGIN
  FOR rs IN (
                SELECT
                        n1,
                        padding
                FROM
                        T1
                WHERE
                        n1 is not null
                AND
                        padding LIKE p_padding
                AND
                        ROWNUM <=5
            )
  LOOP
    PIPE ROW(array_row_type(rs.n1, rs.padding));
  END LOOP;
  RETURN;
END;
/
CREATE OR REPLACE FUNCTION regular_array
(
        p_n1            IN      NUMBER          DEFAULT 0,
        p_padding       IN      VARCHAR2        DEFAULT '%'
)
RETURN array_table_type
AS
v_tab array_table_type := array_table_type();
BEGIN
  FOR rs IN (
                SELECT
                        n1,
                        padding
                FROM
                        T1
                WHERE
                        n1 is not null
                AND
                        padding LIKE p_padding
                -- AND
                         -- ROWNUM <=5
            )
  LOOP
    v_tab.extend;
    v_tab(v_tab.last) := array_row_type(rs.n1,rs.padding);
  END LOOP;
  RETURN v_tab;
END;
/
exit

And then I run the following code:

SELECT * FROM
(
       SELECT
               T1.n1,
               T1.small_vc,
               t.padding
        FROM
                -- table(regular_array) t,
                table(array) t,
                T1
        WHERE
                t.n1 = T1.n1
)
        WHERE
                ROWNUM <=10;

Which is what I wanted. Any suggestions to improve the code is welcome.

Thanks,

Learner

 

[Dagon]

If n1 is not unique (which it plainly isn't), then regardless of whether you use pipeline functions or any other technique to join the table, you are going to end up with a cartesian product.

Perhaps if you tell us what you are really trying to achieve, we might be able to help a bit better. So far I've got:

a) You randomly select 5 rows from table T1.
b) You join these 5 random rows back to T1 on a non-unique key.
c) Because you are getting lots of rows returned, you put a restriction that you only want the first 10 rows.

I'm struggling to conceive of any way that this algorithm could be useful in any situation, but maybe you can enlighten us.

BTW, if you'd simply wanted to avoid creating a temporary table, you could just have done this:

SELECT * FROM
     (
       SELECT
                T1.n1
              , T1.small_vc
              , mytable.padding
        FROM
               (select n1,padding from T1 where rownum <=5) mytable
              , T1
       WHERE
             mytable.n1 = T1.n1
    )
WHERE ROWNUM <= 10;