Post HTTP form to PL/SQL Procedure

Scenario: I needed to have users visit an external website for payment processing. When the other website finished doing it's thing, it would post feedback data to a url that I provided. The return url is static and cannot be changed dynamically. I needed to be able to accept the posted data and apply to my htmldb application.

Click the following button for a demonstration:

HTML Source

<form Method="POST" action="http://apex.oracle.com/pls/apex/eberhard.view_http_post_fixed" name="form1">
	<P><b>This form has three parameters, which matches the number of parameters the procedure view_http_post_fixed has.</b>
	<table><tr><td>Session Id:</td>
             	   <td><input type="text" name="SESSION_ID" value="9582274473829998340"></td>
		</tr>
		<tr><td>Key:</td>
			<td><input type="text" name="KEY" value="1109"></td>
		</tr>
		<tr><td>Name:</td>
			<td> <input type="text" name="NAME" value="Jeff Eberhard"></td>
		</tr></table>
<input value="view_http_post_fixed" type="submit">
</form>
<p><b>This form has extra parameters, the procedure view_http_post_fixed will not execute.</b><br>
<form Method="POST" action="http://apex.oracle.com/pls/apex/eberhard.view_http_post_fixed" name="form2">
	<table><tr><td>Session Id:</td>
             	   <td><input type="text" name="SESSION_ID" value="9582274473829998340"></td>
		</tr>
		<tr><td>Key:</td>
			<td><input type="text" name="KEY" value="1109"></td>
		</tr>
		<tr><td>Name:</td>
			<td><input type="text" name="NAME" value="Jeff Eberhard"></td>
		</tr>
		<tr><td>Phone:</td>
			<td> <input type="text" name="PHONE" value="(555) 555-5555"></td>
		</tr></table>
<input value="view_http_post_fixed" type="submit">
</form>
<p><b>This form has missing parameters, the procedure view_http_post_fixed will not execute.</b><br>
<form Method="POST" action="http://apex.oracle.com/pls/apex/eberhard.view_http_post_fixed" name="form2">
	<table><tr><td>Session Id:</td>
             	   <td><input type="text" name="SESSION_ID" value="9582274473829998340"></td>
		</tr>
		<tr><td>Key:</td>
			<td><input type="text" name="KEY" value="1109"></td>
		</tr></table>
<input value="view_http_post_fixed" type="submit">
</form>
<p><b>This form has missing parameters, calls view_http_post_fixed_defaults which has defaults.</b><br>
<form Method="POST" action="http://apex.oracle.com/pls/apex/eberhard.view_http_post_fixed_defaults" name="form2">
	<table><tr><td>Session Id:</td>
             	   <td><input type="text" name="SESSION_ID" value="9582274473829998340"></td>
		</tr>
		<tr><td>Key:</td>
			<td><input type="text" name="KEY" value="1109"></td>
		</tr></table>
<input value="view_http_post_fixed_defaults" type="submit">
</form>
<P><b>This form calls the view_http_post_flex procedure using the ! prefix so the mod_plsql will send parameters as the 2 arrays</b>
<form Method="POST" action="http://apex.oracle.com/pls/apex/!eberhard.view_http_post_flex" name="form3">
	<table><tr><td>Session Id:</td>
             	   <td><input type="text" name="SESSION_ID" value="9582274473829998340"></td>
		</tr>
		<tr><td>Key:</td>
			<td><input type="text" name="KEY" value="1109"></td>
		</tr>
		<tr><td>Name:</td>
			<td><input type="text" name="NAME" value="Jeff Eberhard"></td>
		</tr></table>
<input value="!view_http_post_flex" type="submit">
</form> 

<P><b>This form calls the view_http_post_flex procedure again using more parameters, including hidden parameters.</b>
<form Method="POST" action="http://apex.oracle.com/pls/apex/!eberhard.view_http_post_flex" name="form4">
	<table><tr><td>Session Id:</td>
             	   <td><input type="text" name="SESSION_ID" value="9582274473829998340"></td>
		</tr>
		<tr><td>Key:</td>
			<td><input type="text" name="KEY" value="1109"></td>
		</tr>
		<tr><td>Name:</td>
			<td><input type="text" name="NAME" value="Jeff Eberhard"></td>
		</tr>
		<tr><td>Phone:</td>
			<td> <input type="text" name="PHONE" value="(555) 555-5555"></td>
		</tr></table>
<input type="hidden" name="RATE" value="$250/hr">
<input type="hidden" name="USERFLD1" value="Available">
<input type="hidden" name="USERFLD2" value="Anytime">
<input value="!view_http_post_flex" type="submit">
</form>

PL/SQL Code

Procedures

create or replace procedure VIEW_HTTP_POST_FIXED
  ( SESSION_ID IN varchar2,
    KEY        IN varchar2,
    NAME       IN varchar2 )
is
begin
        htp.htmlOpen; 
        htp.headOpen; 
        htp.title('Sample Page - of flexible parameters'); 
        htp.headClose; 
        htp.bodyOpen; 
        htp.p('Session ID: '||SESSION_ID);
        htp.nl;
        htp.p('Key: '||KEY);
        htp.nl;
        htp.p('Name: '||NAME);
        htp.bodyClose; 
        htp.htmlClose; 
end;
/
grant execute on VIEW_HTTP_POST_FIXED to public
/
create or replace procedure VIEW_HTTP_POST_FIXED_DEFAULTS
  ( SESSION_ID IN varchar2 DEFAULT 'MISSING',
    KEY        IN varchar2 DEFAULT 'MISSING',
    NAME       IN varchar2 DEFAULT 'MISSING')
is
begin
        htp.htmlOpen; 
        htp.headOpen; 
        htp.title('Sample Page - of flexible parameters'); 
        htp.headClose; 
        htp.bodyOpen; 
        htp.p('Session ID: '||SESSION_ID);
        htp.nl;
        htp.p('Key: '||KEY);
        htp.nl;
        htp.p('Name: '||NAME);
        htp.bodyClose; 
        htp.htmlClose; 
end;
/
grant execute on VIEW_HTTP_POST_FIXED_DEFAULTS
/
create or replace procedure view_http_post_flex (name_array IN owa_util.ident_arr, 
                   value_array IN owa_util.ident_arr) is 
begin 
        htp.htmlOpen; 
        htp.headOpen; 
        htp.title('View HTTP posted form - flexible'); 
        htp.headClose; 
        htp.bodyOpen; 
        htp.p('<table border="1"><tr><th>Parameter</th><th>Value</th></tr>');

        for i in name_array.first..name_array.last loop 
                htp.p('<tr><td>'||name_array(i)||'</td>');
                htp.p('<td>'||value_array(i)||'</td></tr>');
        end loop; 
        htp.p('</table>');
        htp.bodyClose; 
        htp.htmlClose; 
end;
/
grant execute on view_http_post_flex to public
/
create or replace procedure view_http_post_flex_redirect (name_array IN owa_util.ident_arr, 
                   value_array IN owa_util.ident_arr) is 

  v_key varchar2(100);
  v_name varchar2(100);
  v_phone varchar2(100);
  v_rate  varchar2(100);
  v_userfld1 varchar2(100);
  v_userfld2 varchar2(100);
  
begin 
                for i in name_array.first..name_array.last loop 
                case name_array(i)
                  when 'KEY' then v_key := value_array(i);
                  when 'NAME' then v_name := value_array(i);
                  when 'PHONE' then v_phone := value_array(i);
                  when 'RATE' then v_rate := value_array(i);
                  when 'USERFLD1' then v_userfld1 := value_array(i);
                  when 'USERFLD2' then v_userfld2 := value_array(i);
                  else NULL;
                end case;
        end loop; 

       owa_util.redirect_url('f?p=9487:65:::::P65_KEY,P65_NAME,P65_PHONE,P65_RATE,P65_USERFLD1,P65_USERFLD2:'||
                              v_key||','||v_name||','||v_phone||','||v_rate||','||v_userfld1||','||v_userfld2);
end;
/
grant execute on view_http_post_flex_redirect to public
/