We'll have to first massage this data into a payload to send. For that, we declare answers as the the collection of all the HTML elements with answers as the name. Then we declare payload as an object with one property, answers. The value of that, will be answers.
app/controllers/poll_controller.rb
def submit
answers = params[:answers]
payload = { answers: answers }
end
answers = params[:answers]
payload = { answers: answers }
end
We then use HTTParty to POST, just like we used it to send a GET request earlier. The base URL is the same - we'll use ORDS_API_URL. For the body, we use payload after running the to_json() method on it, to convert it to a JSON object. And because of this, we should specify that it's JSON in the headers object. The result is returned in the variable response.
app/controllers/poll_controller.rb
def submit
answers = params[:answers]
payload = { answers: answers }
response = HTTParty.post(
ORDS_API_URL,
body: payload.to_json,
headers: {
"Content-Type" => "application/json"
}
)
end
answers = params[:answers]
payload = { answers: answers }
response = HTTParty.post(
ORDS_API_URL,
body: payload.to_json,
headers: {
"Content-Type" => "application/json"
}
)
end
Now, if it's successful, the code property of response will be 200. In that case flash a green success message. If not, flash a red error message.
app/controllers/poll_controller.rb
def submit
answers = params[:answers]
payload = { answers: answers }
response = HTTParty.post(
ORDS_API_URL,
body: payload.to_json,
headers: {
"Content-Type" => "application/json"
}
)
if response.code == 200
flash[:notice] = "Submission successful!"
else
flash[:alert] = "API error."
end
end
answers = params[:answers]
payload = { answers: answers }
response = HTTParty.post(
ORDS_API_URL,
body: payload.to_json,
headers: {
"Content-Type" => "application/json"
}
)
if response.code == 200
flash[:notice] = "Submission successful!"
else
flash[:alert] = "API error."
end
end
When all's said and done, use the redirect_to statement to return to root_path, which is the poll form.
app/controllers/poll_controller.rb
def submit
answers = params[:answers]
payload = { answers: answers }
response = HTTParty.post(
ORDS_API_URL,
body: payload.to_json,
headers: {
"Content-Type" => "application/json"
}
)
if response.code == 200
flash[:notice] = "Submission successful!"
else
flash[:alert] = "API error."
end
redirect_to root_path
end
answers = params[:answers]
payload = { answers: answers }
response = HTTParty.post(
ORDS_API_URL,
body: payload.to_json,
headers: {
"Content-Type" => "application/json"
}
)
if response.code == 200
flash[:notice] = "Submission successful!"
else
flash[:alert] = "API error."
end
redirect_to root_path
end
Next, let's go back to Oracle APEX. Remember we created the GET handler for the API endpoint "poll/:id"? Well, now create a POST handler.
Make sure the id variable is defined, and we tell Oracle APEX that it's to be found in the URL.
Before we examine the PL/SQL code for the handler, this is the shape of the data that will be sent, as an example.
{
"Answers": {
"1": "4",
"2": "5",
"3": "1",
"4": "2",
"5": "1",
"6": "4",
"7": "4",
"8": "3",
"9": "3",
"10": "5"
}
}
"Answers": {
"1": "4",
"2": "5",
"3": "1",
"4": "2",
"5": "1",
"6": "4",
"7": "4",
"8": "3",
"9": "3",
"10": "5"
}
}
We have a DECLARE, BEGIN and END statements. After DECLARE, we declare l_request_body_clob as a CLOB object. A CLOB is a Character Large Object, which pretty much describes the data that will be sent to Oracle APEX via the form. Then we declare l_keys as an array used to store strings. (If you're curious, the "l" prefix is used to say "local". Seems superfluous, but it's Oracle's convention, so...)
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
END;
body_text refers to the data sent in the form, via the API endpoint. This value is assigned to the variable l_request_body_clob.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
END;
Then we use the parse() method of the APEX_JSON object, passing in l_request_body_clob as the p_source parameter's value. This, in effect, parses the form body data.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
END;
Now for l_keys. We want to get the keys from the answers object. So we use the get_members() method of the APEX_JSON object (which already parsed the form data) and specify that the name of the object is "answers" by setting that as the parameter value of p_path. This in effect produces an array of all the keys in the form data, and binds that value to the array l_keys.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
END;
To be safe, we have an IF block to check that l_keys is a valid non-empty array. Then we iterate through l_keys.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
BEGIN
END;
END LOOP;
END IF;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
BEGIN
END;
END LOOP;
END IF;
END;
We'll declare the serial number and answer here, in the variables l_serial_no and l_answer_value respectively. We know that those are just numbers and they won't go above 10, so "VARCHAR2(2)" is safe enough.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(2);
l_answer_value VARCHAR2(2);
BEGIN
END;
END LOOP;
END IF;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(2);
l_answer_value VARCHAR2(2);
BEGIN
END;
END LOOP;
END IF;
END;
Then we assign the value of the current element of l_keys, to l_serial_no. And we use the get_varchar2() method of APEX_JSON, again using "answers." and the current value of l_serial_number ("||" is actually concatenation in PL/SQL, so we're trying to read the value of answers.1, answers.2, and so on.) as the value of p_path, and assign the value to l_answer_value. Phew! That was a mouthful. But you get the idea... I hope.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(2);
l_answer_value VARCHAR2(2);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
END;
END LOOP;
END IF;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(2);
l_answer_value VARCHAR2(2);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
END;
END LOOP;
END IF;
END;
And we write an INSERT statement that adds a row with the values of l_serial_no and l_answer_value. Because QUESTION_SERIAL_NO is an integer, we need to use the TO_NUMBER() function on l_serial_no. POLL_ID will be the variable id in the POST handler.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(2);
l_answer_value VARCHAR2(2);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
INSERT INTO POLL_RESULTS (POLL_ID, QUESTION_SERIAL_NO, RESULT)
VALUES (:id, TO_NUMBER(l_serial_no), l_answer_value);
END;
END LOOP;
END IF;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(2);
l_answer_value VARCHAR2(2);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
INSERT INTO POLL_RESULTS (POLL_ID, QUESTION_SERIAL_NO, RESULT)
VALUES (:id, TO_NUMBER(l_serial_no), l_answer_value);
END;
END LOOP;
END IF;
END;
The we use the open_object(), write() and close_object() of APEX_JSON to set status and message as a response.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(255);
l_answer_value VARCHAR2(4000);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
INSERT INTO POLL_RESULTS (POLL_ID, QUESTION_SERIAL_NO, RESULT)
VALUES (:id, TO_NUMBER(l_serial_no), l_answer_value);
END;
END LOOP;
END IF;
APEX_JSON.open_object;
APEX_JSON.write('status', 'success');
APEX_JSON.write('message', 'Answers processed successfully');
APEX_JSON.close_object;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(255);
l_answer_value VARCHAR2(4000);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
INSERT INTO POLL_RESULTS (POLL_ID, QUESTION_SERIAL_NO, RESULT)
VALUES (:id, TO_NUMBER(l_serial_no), l_answer_value);
END;
END LOOP;
END IF;
APEX_JSON.open_object;
APEX_JSON.write('status', 'success');
APEX_JSON.write('message', 'Answers processed successfully');
APEX_JSON.close_object;
END;
Then we have a provision for if anything goes wrong.
DECLARE
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(255);
l_answer_value VARCHAR2(4000);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
INSERT INTO POLL_RESULTS (POLL_ID, QUESTION_SERIAL_NO, RESULT)
VALUES (:id, TO_NUMBER(l_serial_no), l_answer_value);
END;
END LOOP;
END IF;
APEX_JSON.open_object;
APEX_JSON.write('status', 'success');
APEX_JSON.write('message', 'Answers processed successfully');
APEX_JSON.close_object;
EXCEPTION
WHEN OTHERS THEN
APEX_JSON.open_object;
APEX_JSON.write("status", "error");
APEX_JSON.write("message", "PL/SQL Error: " || SQLERRM);
APEX_JSON.close_object;
END;
l_request_body_clob CLOB;
l_keys APEX_T_VARCHAR2 := APEX_T_VARCHAR2();
BEGIN
l_request_body_clob := :body_text;
APEX_JSON.parse(p_source => l_request_body_clob);
l_keys := APEX_JSON.get_members(p_path => 'answers');
IF l_keys IS NOT NULL AND l_keys.COUNT > 0 THEN
FOR i IN 1..l_keys.COUNT LOOP
DECLARE
l_serial_no VARCHAR2(255);
l_answer_value VARCHAR2(4000);
BEGIN
l_serial_no := l_keys(i);
l_answer_value := APEX_JSON.get_varchar2(p_path => 'answers.' || l_serial_no);
INSERT INTO POLL_RESULTS (POLL_ID, QUESTION_SERIAL_NO, RESULT)
VALUES (:id, TO_NUMBER(l_serial_no), l_answer_value);
END;
END LOOP;
END IF;
APEX_JSON.open_object;
APEX_JSON.write('status', 'success');
APEX_JSON.write('message', 'Answers processed successfully');
APEX_JSON.close_object;
EXCEPTION
WHEN OTHERS THEN
APEX_JSON.open_object;
APEX_JSON.write("status", "error");
APEX_JSON.write("message", "PL/SQL Error: " || SQLERRM);
APEX_JSON.close_object;
END;
Time to test this! Fill in the poll and click SEND.
You should see this!
And the results in Oracle APEX's database, in the POLL_RESULTS table!
One more thing...
An anti-CSFR token is very easy to apply in Ruby On Rails. Just go to this file. When you rerun your form, you should see a hidden field if you view the source. Everything else is taken care of, including the validation.app/controllers/application_controller.rb
class ApplicationController < ActionController::Base
allow_browser versions: :modern
protect_from_forgery with: :exception
end
allow_browser versions: :modern
protect_from_forgery with: :exception
end
