In this post we will cover the creation of Faceted Search reports in Oracle APEX (19.2) from datasets stored in buckets in the Oracle Cloud.
The dataset obtained for this example can be found on the Brazilian Open Data Portal. These are data related to occupational accidents classified by age group. The dataset is of the CSV type.
After obtaining the dataset, we create a bucket on the Oracle Cloud, where the file will be stored. The bucket is created and the dataset is stored using the following steps:
1. We open the Object Storaje menu, on Oracle Cloud.
2. On the Object Storage page, we click Create Bucket.
3. In the bucket creation form, we define a bucket name and some additional settings, as needed. In our example, the name defined for the bucket was bucket-occupational-accidents.
3. Inside the bucket we created, we import the file containing the occupational accident data, clicking the Upload Objects button. In our example, the ACT03 file.
Creating External Tables
After following the steps, we need to transform the imported dataset into a table in the Oracle Autonomous Database. For this, in this example we use the procedure CREATE_EXTERNAL_TABLE of the package DBMS_CLOUD that is provided with the Autonomous Database.
Note: To execute DBMS_CLOUD subprograms other than ADMIN, it is necessary to grant EXECUTE privileges to the user who will use it.
GRANT EXECUTE ON DBMS_CLOUD TO username;One of the necessary parameters in the CREATE_EXTERNAL_TABLE procedure is CREDENTIAL_NAME, which is the name of the credential to access Cloud Object Storage. Therefore, we need to create a credential, as shown in the command below.
begin
dbms_cloud.create_credential(
credential_name => 'cr_apex_dataset'
,username => 'username_oracle_cloud'
,password => 'user-toke-apeks'
);
end;For the username parameter, we inform the Oracle Cloud Account username. For the password, we generate an authentication token. This token is generated on the User Details page, in the Auth Token resource, by clicking on Generate Token.
In the modal that will open, just provide a description for the token. The generated token must be copied and pasted in the password parameter.
Once the credential is created, it is time to create the external table based on the dataset. Before executing the procedure CREATE_EXTERNAL_TABLE we need the URI of the stored file. just open the bucket we created and view the details of the stored object.
In the modal that will be opened we will see the URL Path (URI) that will be used in the creation of the external table.
When executing CREATE_EXTERNAL_TABLE procedure, we created the external table ACCIDENTS_BY_AGE with 6 columns corresponding to the columns of the ACT03 object and inform the data types of each one.
begin
dbms_cloud.create_external_table(
table_name => 'accidents_by_age'
,credential_name => 'cr_apex_dataset'
,file_uri_list => 'https://objectstorage.url-path/ACT03.csv'
,format => json_object(
'type' value 'csv'
,'skipheaders' value '1'
,'recorddelimiter' value newline'
,'conversionerrors' value 'reject_record'
,'characterset' value 'WE8MSWIN1252'
,'trimspaces' value 'lrtrim'
,'rejectlimit' value '1')
,column_list => ('year number
,age varchar2(500)
,reason_situation varchar2(500)
,sex varchar2(500)
,total_accidents number
,situation varchar2(500)')
);
end;When consulting the table in the Object Browser of Oracle APEX, we can see the table created with all the data from the dataset.
Now we can create a faceted search report.
Below we see the result.
Note: Another way to create this table from a CSV file would be by uploading data to Oracle Apex through the Data Workshop but this is a subject for another post.
In practice...
To see the Faceted Search used in this example, go to the demo application below.
Demo Application
Link: Learning APEX App
User (default): demo
Password: demo