Archive for April 20th, 2005

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:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"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:

  PROCEDURE process_select (p_select_values IN  owa_util.ident_arr) IS
  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

Comments No Comments »