Forum Stats

  • 3,815,685 Users
  • 2,259,067 Discussions


PL/SQL function(s) for Mobile Verification using APEX_WEB_SERVICE

Mahmoud_Rabie Cloud Solution Architect,Member Posts: 3,216 Bronze Crown
edited Jul 10, 2017 5:13AM in APEX Discussions

Hello Experts


- Nexmo SMS Service Provider used to verify mobile number. Based on documentation, just two simple steps are needed.

  1. Send the verify code to the number you want to verify:
curl -X POST \ -d api_key=MYAPIKEY \ -d api_secret=MYAPISECRET \ -d number=MYMOBILENUMBER \ -d brand="MYBRAND"



  1. Submit the Request ID generated by step 1 and the code received via SMS to complete the verification:
curl -X POST \ -d api_key=MYAPIKEY \ -d api_secret=MYAPISECRET \ -d request_id="REQUEST_ID" \ -d code=CODE




Using APEX_WEB_SERVICE package, I need to write one or two PL/SQL Functions that take the mobile number and inputs and does these two POSTs and then return the result

I would appreciate any help



Kiran Pawar


  • Mahmoud_Rabie
    Mahmoud_Rabie Cloud Solution Architect, Member Posts: 3,216 Bronze Crown
    edited Jul 7, 2017 2:29PM
  • Pavel_p
    Pavel_p Member Posts: 2,312 Gold Trophy
    edited Jul 10, 2017 5:13AM

    Hello Mahmoud,

    there is already a very similar thread about invoking nexmo api , so review it please. Assuming you have already accomplished prerequisities (ACLs+wallet/reverse proxy), you should be able to invoke the api like this:

    declare  --sample request (supported methods should be get and post)  --  k_base_url        constant varchar2(100) := '';  k_api_key        constant varchar2(20) := 'my_api_key';  k_api_secret      constant varchar2(20) := 'my_api_secret';  k_brand          varchar2(20) := 'MyApp';  l_number          varchar(20) := 'my_number';  l_url            varchar2(500);  l_json_response  clob;  l_values          apex_json.t_values;begin   --concatenate url or you can use p_parm_name and p_parm_value parameters  l_url := k_base_url ||'?api_key=' ||k_api_key ||'&api_secret=' ||k_api_secret ||'&number=' ||l_number ||'&brand=' ||k_brand;  l_json_response := apex_web_service.make_rest_request(    p_url          => l_url,    p_http_method  => 'GET'  );  dbms_output.pul_line('Returned JSON: ' || l_json_response);  /*output should be something like this:    {      "request_id":"requestId",      "status":"status",      "error_text":"error"}    */  apex_json.parse(    p_values  => l_values,    p_source  => l_json_response  );  dbms_output.put_line('request_id: ' ||apex_json.get_varchar2(    p_values  => l_values,    p_path    => 'request_id'  ) );  dbms_output.put_line('status: ' ||apex_json.get_varchar2(    p_values  => l_values,    p_path    => 'status'  ) );  dbms_output.put_line('error_text: ' ||apex_json.get_varchar2(    p_values  => l_values,    p_path    => 'error_text'  ) );  --now you have your request id and status, so invoke the second api function analogicallyend;

    Unfortunately I cannot test the code, so maybe you'll have to make some modifications/fixes.



    Kiran Pawar
This discussion has been closed.