Survey Email

Purpose: Demonstrate emailing a HTML survey and then retreiving the posted results from the survey

Forum Link

An email will be sent to the address below with HTML code in the message. The HTML includes a form to be filled in by the user and then submits the form to a PL/SQL procedure which will save the posted values and redirect back to this page to display the results. The technique used of submitting a HTTP post request to a PL/SQL procedure is discussed in the topic, Post HTTP form to PL/SQL Procedure

Note: It may take a while for you to get the email

Email Address:

Page Process (to send email)

DECLARE
 v_mysurvey_id NUMBER;
 v_message VARCHAR2(2000);
BEGIN
  INSERT INTO my_survey ( status) 
       VALUES ( 'SENT' )
    RETURNING mysurvey_id INTO v_mysurvey_id;

 v_message := '<HTML><BODY>Please complete the following survey.  Thanks!!!!!<P>
<form Method="POST" action="http://apex.oracle.com/pls/apex/!schema_name.post_mysurvey" name="surveyform">
	<table><tr><td>What is your job title?</td>
             	   <td><input type="text" name="ANS1" size="25" maxlength="50"></td>
		</tr>
		<tr><td>What percent of your job is APEX related:</td>
			<td><select name="ANS2">
                            <option value="10">&lt; 10%</option>
                            <option value="25">&lt; 25%</option>
                            <option value="50">&lt; 50%</option>
                            <option value="75">&lt; 75%</option>
                            <option value="100">&gt; 75%</option>
                            </select>
                        </td>
		</tr>
		<tr><td>How many APEX applications have you created/support:</td>
			<td><select name="ANS3">
                            <option value="1">1-5</option>
                            <option value="5">6-10</option>
                            <option value="10">11-25</option>
                            <option value="25">26-50</option>
                            <option value="50">&gt; 50</option>
                        </td>
		</tr>
		<tr><td>Name the best new feature of APEX 3.0:</td>
			<td> <input type="text" name="ANS4" size="100" maxlength="255"></td>
		</tr>
		<tr><td>Name the most annoying thing about APEX:</td>
			<td> <input type="text" name="ANS5" size="100" maxlength="255"></td>
		</tr></table>
<input type="hidden" name="MYSURVEY_ID" value="'||v_mysurvey_id||'">
<input value="Submit Survey" type="submit">
</form>';
 
   HTMLDB_MAIL.SEND(
    P_TO        => :P28_EMAIL,
    P_FROM      => 'not_real@email.org',
    P_BODY      => v_message,
    P_BODY_HTML => v_message,
    P_SUBJ      => 'APEX Survey Demo');
END;

PL/SQL Procedure

After creating the procedure make sure to "GRANT EXECUTE ON <procedure_name> TO PUBLIC;"

create or replace procedure post_mysurvey (name_array IN owa_util.ident_arr, 
                   value_array IN owa_util.ident_arr) is 
                 
  v_mysurvey_id varchar2(100);
  v_ans1 varchar2(100);
  v_ans2 varchar2(100);
  v_ans3 varchar2(100);
  v_ans4 varchar2(255);
  v_ans5 varchar2(255);
  v_status varchar2(25);
  
begin 
        for i in name_array.first..name_array.last loop 
                case name_array(i)
                  when 'MYSURVEY_ID' then v_mysurvey_id := value_array(i);
                  when 'ANS1' then v_ans1 := value_array(i);
                  when 'ANS2' then v_ans2 := value_array(i);
                  when 'ANS3' then v_ans3 := value_array(i);
                  when 'ANS4' then v_ans4 := value_array(i);
                  when 'ANS5' then v_ans5 := value_array(i);
                  else NULL;
                end case;
        end loop; 
        
        SELECT nvl(MAX(status),'MISSING') 
          INTO v_status
          FROM my_survey
         WHERE mysurvey_id = v_mysurvey_id;
         
        IF ( v_status = 'SENT' ) THEN
          INSERT INTO my_survey_results (mysurvey_id, ans1, ans2, ans3, ans4, ans5 )
               VALUES ( v_mysurvey_id, v_ans1, v_ans2, v_ans3, v_ans4, v_ans5);
          UPDATE my_survey 
             SET status = 'ANSWERED'
           WHERE mysurvey_id = v_mysurvey_id;
        END IF;
       owa_util.redirect_url('f?p=9487:28:::::P28_MYSURVEY_ID,P28_STATUS:'||
                                v_mysurvey_id||','||v_status);
end;