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
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.