Skip to Main Content
  • Questions
  • performing a select on a table using the contents of owa_util.ident_arr

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, dave.

Asked: May 15, 2020 - 2:37 pm UTC

Answered by: Connor McDonald - Last updated: May 19, 2020 - 11:06 am UTC

Category: PL/SQL - Version: 11.2

Viewed 100+ times

You Asked

my apologies a test case via LiveSQL is uploaded, but maybe not workable due to use of compiling package for mod_plsql in OAS 9 (working to move to ORDS). also, it is not so clearly providing what URL I may give here for your easy reference.

I have an array from a multi-select SELECT list in html. It is populating fine, it is passing fine, and using the oft-provided access method of looping through, I can see all expected content.

The problem needing help is one of actually using the collection as part of a larger query. I'm not finding any real help for formatting a query which accepts the array name as part of the query. I have read the docs that the ident_arr is an indexed table of varchar2. OK, but I have no luck SELECTing from ident_arr MYARRAY in such form as "SELECT * FROM myarray;" And even if I'm fortunate to have a working example on this need, I don't see anything indicates how to reference the data value - notice that I had to give "SELECT *" in my sample, but what is the column name? Do I just not have the full picture on using this collection type?

Will I get an answer that says define a type within my package and put my ident_arr into it for another form of suage toward my goal?

This arises from needing to use the 1+ values selected in the list as parts of an IN list for a larger query in the form action procedure.


Added by later edit:

OK, I found an approach that gets me what I wanted, after much research, trial and error, and weeping and gnashing of teeth, but I still might like to know if I have chosen the best approach, or can this be improved:

1. define a fixed type for a generic table of varchar2(200)  -- should cover most needs for this
2. run a tiny loop to load my ident_arr into a new_array_object typed as on of these generic tables
3. use in my WHERE clause:  AND col1 IN (SELECT * FROM TABLE(new_array_object))


--==============================================================================
CREATE OR REPLACE PACKAGE dxs_test AS
--==============================================================================

null_array                          owa_util.ident_arr;

TYPE region_list IS TABLE OF VARCHAR2(100);

--------------------------------------------------------------------------------

PROCEDURE main;

PROCEDURE user_maint(
    var_reg                         owa_util.ident_arr DEFAULT null_array
);

--==============================================================================
END dxs_test;
--==============================================================================
/
show errors

--==============================================================================
CREATE OR REPLACE PACKAGE BODY dxs_test AS
--==============================================================================

PROCEDURE main
IS

BEGIN

    htp.p('<html><head><title>'||owa_util.get_procedure||'</title></head>');
    htp.p('<body><center>');

    htp.p('<form action="dxs_test.user_maint" method=get>');

    htp.p('<table cellspacing=3>');
    htp.p('<tr><td>RVP/Sales Dir</td>');
    htp.p('<td><select multiple name=var_reg>');
    htp.p('<option value=1>option 1</option>');
    htp.p('<option value=2>option 2</option>');
    htp.p('<option value=5>option 3</option>');
    htp.p('</select></td></tr>');
    htp.p('</table>');

    htp.p('<input type=submit value="View">');
    htp.p('</form>');

    htp.p('</center></body></html>');

END main;

--------------------------------------------------------------------------------

PROCEDURE user_maint(
    var_reg                         owa_util.ident_arr DEFAULT null_array
) IS

    l_list                          VARCHAR2(400);

    CURSOR get_tables (x VARCHAR2) IS
        SELECT  *
        FROM    (SELECT rownum rn,table_name
                FROM all_tables WHERE rownum < 10)
        WHERE   rn = x;

BEGIN

    htp.p('<html><head><title>'||owa_util.get_procedure||'</title></head>');
    htp.p('<body><center>');

    htp.p('<table align=center border=1 cellspacing=0 cellpadding=2'||
        'style="border-collapse:collapse;" bordercolor=gray width=340>');

    FOR i IN 1..var_reg.last LOOP
        htp.p('<tr>');
        FOR n IN get_tables (var_reg(i)) LOOP
            htp.p('<td>'||n.table_name||'</td>');
        END LOOP;
        htp.p('</tr>');
        l_list := l_list||var_reg(i)||',';
    END LOOP;

    htp.p('<tr><td>'||l_list||'</td></tr>');

--     FOR m IN (SELECT * FROM var_reg) LOOP                -- "table doesnt exist"
--         htp.p('<tr><td>wow! '||m.var_reg||'</td></tr>'); -- wrong reference!
--     END LOOP;

--     FOR m IN
--         (SELECT table_name FROM
--             (SELECT rownum rn,table_name FROM all_tables
--             WHERE owner = 'WWW_USER' ORDER BY 2)
--         WHERE rn IN (SELECT * FROM TABLE(var_reg))       -- ultimate goal
--         ) LOOP
--         htp.p('<tr><td>wow! '||m.table_name||'</td></tr>');
--     END LOOP;

    htp.p('</table>');
    htp.p('</center></body></html>');

END user_maint;

--==============================================================================
END dxs_test;
--==============================================================================
/
show errors



and we said...

The definition is that of an asscociative array

type ident_arr is table of varchar2(30) index by binary_integer;


which you cannot query directly in 11.2 (Here's a reason to upgrade - you *can* in later versions)

But yes, you've headed down the right path and well done for digging out a solution. For an array type of scalars, the column name is "COLUMN_VALUE", eg

SQL> create or replace type nt as table of number
  2  /

Type created.

SQL> select * from table(nt(1,2,3,4,5));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5


If you have a lot of entries and you don't want to double up on the temporary memory, you can use a pipeline function to avoid having to duplicate the structure, eg



SQL> create or replace
  2  function ident_to_nt(o owa_util.ident_arr) return nt pipelined is
  3  begin
  4    for i in o.first .. o.last loop
  5      pipe row ( o(i));
  6    end loop;
  7    return;
  8  end;
  9  /

Function created.

SQL>
SQL> set serverout on
SQL> declare
  2    l_arr owa_util.ident_arr;
  3  begin
  4    l_arr(1) := '10';
  5    l_arr(2) := '20';
  6    l_arr(3) := '30';
  7
  8    for i in ( select * from table(ident_to_nt(l_arr)))
  9    loop
 10      dbms_output.put_line(i.column_value);
 11    end loop;
 12  end;
 13  /
10
20
30

PL/SQL procedure successfully completed.



and you rated our response

  (1 rating)

Reviews

May 19, 2020 - 1:56 am UTC

Reviewer: d scott from arlington, tx

excellent reply and helpful details to understand the distinction of various elements involved.
Connor McDonald

Followup  

May 19, 2020 - 11:06 am UTC

glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.