Manage Countries Coordinates and Flags in Database using RESTful Web Services and PLSQL



Here i am giving an example to manage countries coordinates and flag in database using RESTful web services and PLSQL.

Reference APIs:

CREATE TABLE fxgn_country_coordinates
  (
    id                           NUMBER,
    country_code        VARCHAR2(10),
    country                  VARCHAR2(200),
    capital                    VARCHAR2(100),
    latitude                   NUMBER,
    longitude                NUMBER,
    updated_by             VARCHAR2(30),
    updated_on             TIMESTAMP (6),
    country_flag_flat    VARCHAR2(100),
    country_flag_shiny VARCHAR2(100)
  );
 
Step 2: Create sequence as below,
 
CREATE SEQUENCE fxgn_country_coordinates_s MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE nokeep noscale GLOBAL;

Step 3: Create stored procedure as below, which will call API to get countries coordinates and flag.

CREATE OR REPLACE
PROCEDURE get_country_coordinates 
AS  
  l_response CLOB;
  l_url VARCHAR2(1000);
  BEGIN
    l_url:= 'https://gist.githubusercontent.com/erdem/8c7d26765831d0f9a8c62f02782ae00d/raw/248037cd701af0a4957cce340dabb0fd04e38f4c/countries.json';
   
    apex_web_service.g_request_headers.DELETE ;
    apex_web_service.g_request_headers(1).NAME  := q'#User-Agent#';
    apex_web_service.g_request_headers(1).VALUE := q'#Oracle Application Express / REST Client Assistant#';
    
    DELETE FROM fxgn_country_coordinates;
    
    INSERT INTO fxgn_country_coordinates (id, latitude, longitude, country, country_code, capital, 
                                          country_flag_flat, country_flag_shiny, updated_by, updated_on)
          SELECT fxgn_country_coordinates_s.nextval,
                 latitude,
                 longitude,
                 country,
                 country_code,
                 capital,
                 '<img src="https://www.countryflags.io/'||country_code||'/flat/64.png">',
                 '<img src="https://www.countryflags.io/'||country_code||'/shiny/64.png">',
                 'KARKUVELRAJA.T',
                 LOCALTIMESTAMP
            FROM dual,
                 json_table (apex_web_service.make_rest_request( p_url => l_url, 
                                                                 p_http_method => 'GET'),
                            '$[*]' COLUMNS ( country VARCHAR2(200) path '$.name', 
                                             country_code VARCHAR2(200) path '$.country_code', 
                                             capital VARCHAR2(200) path '$.capital', 
                                             latitude NUMBER path '$.latlng[0]', 
                                             longitude NUMBER path '$.latlng[1]' 
                                            ));
      COMMIT;
END get_country_coordinates
/

Step 3: Call the procedure. It will populate data into respective table.

BEGIN
get_country_coordinates();
END;
/

Output:

Comments

  1. Political signs for sale We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work.

    ReplyDelete

Post a Comment

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

Querying Data From Flat Files in Oracle

Number of Tables by The Number of Rows in Oracle Database

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Highlight Data That Meet Certain Criteria in Oracle APEX Report

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Remove X (Close Mark) from Oracle APEX Modal Dialog

Display User Rating Icons Dynamically in Oracle APEX Report

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Find Where Specific Table or View is Used in Oracle Database