Invoke Web Services using APEX_WEB_SERVICE in Oracle APEX

September 29, 2021Posted inAPEX
  1. Directly pass the JSON in parameter value.
Copy Code
DECLARE
   l_parm_names    apex_application_global.vc_arr2;
   l_parm_values   apex_application_global.vc_arr2;
   l_clob          CLOB;
   P_EMAIL_ID  varchar2(100):='ashishk392@gmail.com';
   P_CC_MAIL varchar2(100):='vikaspandey641@gmail.com';
BEGIN
   l_parm_names (1) := 'input';
   l_parm_values (1) := '{"items":{ "EMAIL_ID": "'||P_EMAIL_ID||'","CC_MAIL":"'||P_CC_MAIL||'"}}'; 
   -- to invoke json , Directly pass Json in parameter value.
 
  htp.p(l_parm_values(1));

  -- Get the XML response from the web service.
  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url         => 'https://apex.oracle.com/pls/apex/ontoor/send_mail/',
    p_http_method => 'GET',
    p_parm_name   => l_parm_names,
    p_parm_value  =>l_parm_values
  );
  -- Display the whole document returned.
  DBMS_OUTPUT.put_line('l_clob=' || l_clob);
   End;
 /
  1. Use the below process for multiple parameter values.
Copy Code
declare
l_clob clob;
begin
 l_clob:= apex_web_service.make_rest_request(
            p_url => 'https://apex.oracle.com/pls/apex/ontoor/mail/send/',
            p_http_method => 'GET',
            p_parm_name => apex_util.string_to_table('P_EMAIL_ID:CC_EMAIL_ID'),
            p_parm_value => apex_util.string_to_table('ashishk392@gmail.com:vikaspandey641@gmail.com'));         
htp.p(l_clob);           
end;
  1. Use the below process for the single parameter value.
Copy Code
DECLARE
  l_parm_names    apex_application_global.vc_arr2;
  l_parm_values   apex_application_global.vc_arr2;
  l_clob          CLOB;
  BEGIN
  l_parm_names (1) := 'P_EMAIL_ID';
  l_parm_values (1) := 'vikaspandey641@gmail.com'; 
htp.p(l_parm_values(1));

 -- Get the XML response from the web service.
 l_clob := APEX_WEB_SERVICE.make_rest_request(
   p_url         => 'https://apex.oracle.com/pls/apex/ontoor/mail/send/',
   p_http_method => 'GET',
   p_parm_name   => l_parm_names,
   p_parm_value  =>l_parm_values
 );
 -- Display the whole document returned.
 DBMS_OUTPUT.put_line('l_clob=' || l_clob);
  End;
  1. Use below Ajax process for single or multiple parameter values.
Copy Code

function sendMail()
 {    //apex.server.process    
    $.ajax({
		   type:'GET',
		   url:'https://apex.oracle.com/pls/apex/ontoor/mail/send/',
		   data:{ P_EMAIL_ID:"ashishk392@gmail.com",CC_EMAIL_ID:"vikaspandey641@gmail.com"},
		   dataType: "json",
		   success:function(data)
				{
				 apex.debug.info('success');
				}
        });
 }
  1. Web services in apex. 
Copy Code
declare
      P_EMAIL_ID        VARCHAR2 (200)  := :P_EMAIL_ID;
      CC_EMAIL_ID        VARCHAR2 (200)  := :CC_EMAIL_ID;     
begin
        htp.p(:P_EMAIL_ID||'-'||:CC_EMAIL_ID);
     IF P_EMAIL_ID is not null  THEN
           SEND_MAIL_API_NEW(P_EMAIL_ID,CC_EMAIL_ID);   --procedure      
     ELSE            
     htp.p('{"RESPONSE":[{"RESPONSECODE":"1","DESCRIPTION":"Must have email id"}]}');          
    END IF;  

   EXCEPTION
      WHEN OTHERS
      THEN
		 htp.p('{"RESPONSE":[{"RESPONSECODE":"1","DESCRIPTION": "' || SQLERRM || '"}]}');         
end;
Tags: