Recently I needed to call an Oracle procedure with a data array parameter.
Oracle has no native array type, so the solution was somehow tricky.
To be more clearly, I had a HTML form with a multiple selection combobox that must be processed server-side, in an Oracle package.
My form was composed by 2 comboboxes with multiple selection (one of them is source and the other one is destination). On submit, all the items from the destination combobox must be sent to a PL/SQL procedure.
The form looks like this:
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<titleMultiple selection test – Oracle PL/SQL Toolkit</title>
<script language="JavaScript" type="text/JavaScript">
function move(Sursa, Dest)
{
var oo;
for(oo=0; oo<Sursa.length; oo++) {
if(Sursa[oo].selected==true) {
var x=new Option(Sursa[oo].text, Sursa[oo].value,
false, false);
Dest.options[Dest.length]=x;
}
}
for(oo=Sursa.length-1; oo>=0; oo–) {
if(Sursa[oo].selected==true) Sursa[oo]=null;
}
}
function select_all(obj) {
var oo;
for(oo=0; oo<obj.length; oo++)
{ obj[oo].selected=true; }
}
</script>
<body>
<form action="http://server/mypackage.process_select" method="POST"
enctype="application/x-www-form-urlencoded" name="form1"
onSubmit="select_all(document.form1.p_select_values);">
<p>Multiple selection test – Oracle PL/SQL Toolkit</p>
<table width="600" border="1" cellspacing="0" cellpadding="0">
<tr>
<td><div align="center">
<select name="listSrc" size="5" multiple id="listSrc">
<option value="right1">Right 1</option>
<option value="right2">Right 2</option>
<option value="right3">Right 3</option>
<option value="right4">Right 4</option>
<option value="right5">Right 5</option>
<option value="right6">Right 6</option>
</select>
</div></td>
<td><p align="center">
<input type="button" name="Button" value=">>"
onCLick="move(document.form1.listSrc,document.form1.p_select_values);
return false;">
</p>
<p align="center">
<input type="button" name="Button2" value="<<"
onCLick="move(document.form1.p_select_values,document.form1.listSrc);
return false;">
</p></td>
<td><div align="center">
<select name="p_select_values" size="5" multiple
id="p_select_values"> </select>
</div></td>
</tr>
</table>
<p>
<input type="submit" value="Submit">
</p>
</form>
</body>
</html>
The procedure must have the same number of parameters like form inputs.
ATTENTION !!! Don’t be fooled by Submit buttons! If you set the name property of a submit button, then the procedure must contain this name as a parameter!
Even Oracle does not offer an array data type, I found in PL/SQL Toolkit a package named owa_util that has a type ident_arr. This type is exactly an array, and the implementation became very easy as shown below:
BEGIN
HTP.htmlOpen;
HTP.headOpen;
HTP.Title(‘Process Select’);
HTP.headClose;
HTP.bodyOpen;
FOR j IN p_select_values.FIRST .. p_select_values.LAST LOOP
HTP.print(p_select_values(j));
HTP.print(‘
‘);
END LOOP;
HTP.bodyClose;
HTP.htmlClose;
Once again, be very carefull with the form parameters!
Andrei
http://www.webxpert.ro
Entries (RSS)