Patrick's Lucky Dip

Setting page parameters from a dynamic page is a frequent request and is extremely useful. Luckily it's not too hard.

You will need a table containing the list of values and a description of these. In the examples below I have assumed that the table is called PARAM_TABLE with two columns: PARAM_VALUE and PARAM_TITLE, but they could be whatever you like.

You will then have to create a procedure which is called from a dynamic page. The steps below try to document this for you:

First create the procedure:


create or replace procedure show_parameter_1 as

begin

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

  htp.p('<form action="http://your.page.url/goes/here" method="GET" >');

  htp.p('<input type="hidden" name="_pageid" value="xxxxx" />');

  htp.p('<input type="hidden" name="_dad" value="yyyyy" />');


  htp.p('<select name="p_param">'); 
  -- set the name to the equal the page parameter exactly
  -- it is case sensitive!

  for rec in (select param_value, param_title 
              from param_table 
              order by param_title)
  loop
 
    htp.p('<option value="' || rec.param_value || '">' 
      || rec.param_title || '</option>');

  end loop;

  htp.p('</select>');

  htp.p('</form>');

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

end;

Create a dynamic page to call the procedure as follows:

<oracle>
begin
  show_parameter_1;
end;
</oracle>

If you want your drop-down select list to be more intelligent, that is, to display the currently selected option, then you will have to use a bound variable and pass it into your procedure.

Here's the new procedure:

create or replace procedure show_parameter_2 (p_param as varchar2) as

begin

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

  htp.p('<form action="http://your.page.url/goes/here" method="GET" >');

  htp.p('<input type="hidden" name="_pageid" value="xxxxx" />');

  htp.p('<input type="hidden" name="_dad" value="yyyyy" />');


  htp.p('<select name="p_param">'); 
  -- set the name to the equal the page parameter exactly

  for rec in (select param_value, param_title 
              from param_table 
              order by param_title)
  loop

    -- test to see if the passed-in parameter value
    -- matches this value from the table
    if rec.param_value = p_param then

      -- yes, so mark it as selected
      htp.p('<option value="' || rec.param_value 
        || '" selected="selected">' 
        || rec.param_title || '</option>');

    else

      -- not a match, so display as normal
      htp.p('<option value="' || rec.param_value || '">' 
        || rec.param_title || '</option>');

    end if;

  end loop;

  htp.p('</select>');

  htp.p('</form>');

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

end;

You will have to edit your dynamic page to take this into account:

<oracle>
begin
  show_parameter_2(:p_param);
end;
</oracle>

This time step through the wizard until you have the opportunity to mark the bound variable :p_param as public. This makes it visible to the portal page.

Another step you must now do is to tell the portal page to match the page parameter to the dynamic page publicly available parameter. You do this in the page properties, on the tab marked parameters. Your new dynamic page should have a black triangle against it: when you click on this you should be able to map the page parameter to the dynamic page.

Patrick Haston
13 November 2007 (updated 15 November 2007)

Patrick's Lucky Dip - Setting Page Parameters