Search for a command to run...
No comments yet. Be the first to comment.
Oracle APEX already includes Font APEX as the default icon library for Universal Theme. In most cases, that is enough. But in one of my APEX apps, I needed a few icons that were not available there, s
Looking to give your Oracle APEX mobile app a modern, native feel? Here’s a quick hack: add a floating action button that stays anchored at the bottom corner just like in popular mobile apps. This sma
APEX 26.1 has been updated to the Universal Theme. While much of the attention in this release is on AI features, I think the UT improvements are just as important for developers like me who care abou
The apex_barcode package offers a PL/SQL API to generate various types of barcodes. You can output barcodes as SVG (Scalable Vector Graphics) values or as PNG file blobs, making it easy to embed them
Oracle APEX makes it easy to get data on screen fast, but often, the default tabular output isn’t enough. What if you want to highlight statuses, add icons, or display badges all without writing compl
JSON (JavaScript Object Notation) is a lightweight data-interchange format primarily for transferring data between systems. In most cases, we parse the JSON object to store the values in relational tables but we can also store the JSON object in a single table column and extract the data using SQL.
In my use case below, using a public API (https://postcodes.io), I will extract and save the data related to a postcode.
To begin, we need to create a table column to store the JSON Document this could be either:
JSON datatype - Oracle 21c Database
VARCHAR2 - small documents
CLOB
BLOB
For Oracle 21c users, JSON datatype is the best option; however, for other database versions, the best option is to use a BLOB column, as it stores the exact JSON document without any character set conversions, resulting in less storage in most cases. VARCHAR2 is only recommended when we can ensure that the JSON document won't exceed the maximum size of 32767.
create table location (
id number generated by default on null as identity
constraint location_id_pk primary key,
postcode varchar2(10),
postcode_json_content blob
);
and a trigger calling the API and save the request as a blob.
create or replace trigger "BI_LOCATION"
before insert on "LOCATION"
for each row
declare
l_json_postcode blob;
begin
l_json_postcode := apex_web_service.make_rest_request_b(
p_url => 'https://api.postcodes.io/postcodes/'||:new.postcode,
p_http_method => 'GET');
:NEW.postcode_json_content := l_json_postcode;
end;
If we insert any valid postcode at this point, the trigger will call the API and save the blob content, but it is only a blob content. If we try to save an image in postcode_json_content, that will work because there is nothing to prevent only JSON valid data. To solve that, we add a constraint as follows.
alter table LOCATION
add constraint postcode_data_json
check ( postcode_json_content is json );
The column postcode_json_content now only accepts valid JSON objects. Now we can add some data.
insert into location(postcode) values ('SW1A1AA');
If a valid JSON is returned from the API, we have the following
select to_clob(postcode_json_content) postcode_json_content from location;
{
"status": 200,
"result": {
"postcode": "SW1A 1AA",
"quality": 1,
"eastings": 529090,
"northings": 179645,
"country": "England",
"nhs_ha": "London",
"longitude": -0.141588,
"latitude": 51.501009,
"european_electoral_region": "London",
"primary_care_trust": "Westminster",
"region": "London",
"lsoa": "Westminster 018C",
"msoa": "Westminster 018",
"incode": "1AA",
"outcode": "SW1A",
"parliamentary_constituency": "Cities of London and Westminster",
"admin_district": "Westminster",
"parish": "Westminster, unparished area",
"admin_county": null,
"admin_ward": "St James's",
"ced": null,
"ccg": "NHS North West London",
"nuts": "Westminster",
"codes": {
"admin_district": "E09000033",
"admin_county": "E99999999",
"admin_ward": "E05000644",
"parish": "E43000236",
"parliamentary_constituency": "E14000639",
"ccg": "E38000256",
"ccg_id": "W2U3Z",
"ced": "E99999999",
"nuts": "TLI32",
"lsoa": "E01004736",
"msoa": "E02000977",
"lau2": "E09000033"
}
}
}
Now we can easily extract the data and specify the correct data type to be returned.
select l.postcode_json_content.result.latitude.number() as latitude,
l.postcode_json_content.result.longitude.number() as longitude,
l.postcode_json_content.result.region.string() as region,
l.postcode_json_content.result.country.string() as country,
l.postcode_json_content.result.parish.string() as longitude
from location l
Important! We need to prefix the columns alias to avoid the error below.
ORA-00904: "POSTCODE_JSON_CONTENT"."RESULT"."LONGITUDE": invalid identifier