Search This Blog

Tuesday, August 7, 2018

RESTful Get API Example in Oracle Apex

DECLARE
   l_param_list      VARCHAR2 (512);
   l_http_request    UTL_HTTP.req;
   l_http_response   UTL_HTTP.resp;
   l_response_text   VARCHAR2 (32767);
   l_response        VARCHAR2 (32767);

   v_url             VARCHAR (500);
   url_param_list    VARCHAR2 (512);

   p_userid          VARCHAR2 (500) := '100140';
   p_sessionid       VARCHAR2 (500) := '123456';
   p_companyid       VARCHAR2 (500) := '101';
   p_executionmode   VARCHAR2 (500) := 'S';
   p_studentid       VARCHAR2 (500) := '9005';
   p_authkey         VARCHAR2 (500) := 123456;
   ts                VARCHAR2 (32000)
      := '{"std_info_node":[{"errormessage":"Invalid ID . Please input valid ID","studentname":"Qaium","accountno":"0487878778","mobileno":"01234","errorcode":"1"}]}';

   l_data_count      NUMBER := 0;
   l_values          apex_json.t_values;

   t_timeout         INTEGER;
BEGIN
   v_url := 'http://10.11.150.12/PmsWebService/StudentInfo?';
   url_param_list :=
         'userid='
      || p_userid
      || '&sessionid='
      || p_sessionid
      || '&companyid='
      || p_companyid
      || '&executionmode='
      || p_executionmode
      || '&studentid='
      || :P180_STUDENT_ID                                        
      || '&authkey='
      || p_authkey;

   /*begin
   l_http_request :=  UTL_HTTP.begin_request (v_url, 'GET', 'HTTP/1.1');
   UTL_HTTP.write_text (l_http_request, l_param_list);
   --  raise_application_error(-20007,'1');
   l_http_response := UTL_HTTP.get_response (l_http_request);
   raise_application_error(-20007,'2 : '||l_http_response.status_code);
   end ; */

   UTL_HTTP.get_transfer_timeout (t_timeout);
   UTL_HTTP.set_transfer_timeout (10);
   UTL_HTTP.get_transfer_timeout (t_timeout);

   l_param_list := NULL;
   l_http_request :=
      UTL_HTTP.begin_request (v_url || url_param_list, 'GET', 'HTTP/1.1');
   UTL_HTTP.write_text (l_http_request, l_param_list);
   l_http_response := UTL_HTTP.get_response (l_http_request);

   UTL_HTTP.read_text (l_http_response, l_response_text);

   IF l_response_text IS NULL
   THEN
      raise_application_error (-20003,
                               'Request Does Not Get Any Response !!!');
   END IF;

   apex_json.parse (l_response_text);

   SELECT    'DCMC'
          || REPLACE (TO_CHAR (SYSDATE, 'DD-MM-YY'), '-', '')
          || DCMC_PAYMENT.NEXTVAL
     INTO :P180_REF_NO
     FROM DUAL;

   :P180_NAME :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].studentname');
   :P180_MOBILE :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].mobileno');
   :P180_AC_NUMBER :=
      apex_json.get_varchar2 (p_path => 'std_info_node[1].accountno');
   --raise_application_error(-20003,:P180_MOBILE);

   /*
    FOR i IN 1 ..3

    LOOP
   :P180_NAME:= apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].studentname') ;
   :P180_MOBILE:= apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].mobileno') ;
   :P180_AC_NUMBER :=apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].accountno') ;

    -- DBMS_OUTPUT.put_line ('a is '|| apex_json.get_varchar2 (p_path => 'std_info_node[' || i || '].studentname')|| l_data_count);

    END LOOP; */

   UTL_HTTP.end_response (l_http_response);
EXCEPTION
   WHEN UTL_HTTP.end_of_body
   THEN
      UTL_HTTP.end_response (l_http_response);
END;

No comments:

Post a Comment

PDF to Text Covert by Oracle Apex

Here are the steps to convert and get the character into a region by Oracle Apex. Step1.  Create a page and Copy-Pest the below code into Pa...