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.

PROCEDURE get_country_coordinates 
  l_response CLOB;
  l_url VARCHAR2(1000);
    l_url:= '';
    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,
                 '<img src="'||country_code||'/flat/64.png">',
                 '<img src="'||country_code||'/shiny/64.png">',
            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]' 
END get_country_coordinates

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




  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.


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