Creating Faceted Search reports in Oracle APEX (19.2) from datasets in Oracle Cloud Object Storage


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


#orclapex #OracleCloud #ObjectStorage

396 views2 comments

Recent Posts

See All
Never Miss a Post. Subscribe Now!

Happy is the man that findeth wisdom, and the man that getteth understanding. (Proverbs 3:13)

Lucas Rayner © 2018 

  • Branco Twitter Ícone
  • Branca Ícone LinkedIn