Skip to main content

61 posts tagged with "orclapex"

View All Tags

· 2 min read
Adrian Png

I have written on two occasions, "Just a Drop of Oracle APEX" and "APEX and the Affordable Cloud!", about how easy it is to get up and running on hosting providers like Linode and DigitalOcean. The approach largely involves Vagrant, and though it is an easy to use tool, it still needed a little bit of DevOps heroism, and sometimes, dealing with API keys may not necessarily be a priority for someone looking for a quick way to setup a consistent and robust Oracle Application Express environment.

· 4 min read
Adrian Png

Verified!

Updated November 18, 2018 Added a help utility to populate an Oracle Wallet with root certificates

Oracle Database 18c Express Edition comes with a usable Oracle Wallet. In a previous article, I had described how to:

  1. Obtain the certificates necessary to make a successful SSL/TLS connection.
  2. Create and add these certificates to the Oracle Wallet.
  3. Configure the APEX instance to use the wallet.

Someone had asked in the comments, what certificates were necessary for Social Sign-in in Oracle Application Express (APEX) to work. It's something that I think most of us would like to know too, so I did a little more digging and here's what I found for three of the more popular social platforms: Google, Facebook and Microsoft.

· 6 min read
Adrian Png

1280 720 APEX and ORE in Action

I wrote, quite a while ago, on how Oracle Application Express (APEX) developers could integrate R functionality/code in their applications. This was done primarily through Node.js packages and exposed as RESTful Web Services. Not the ideal solution, but useful for people wanting to integrate R statistical calculations and complex charting, but could not afford the hefty price tag for an Oracle Database Enterprice Edition license, plus the Oracle Advanced Analytics database option.

The revolutionary 18c release of the Oracle Database Express Edition (XE) significantly changed the Data Analytics playing field. The no-cost platform has resource caps (2 CPU, 2 GB RAM and 12 GB data storage). It's not going to help perform complex GWAS (Genome-wide Association Study), but should suffice for many smaller research projects, statistical reporting and creating useful prediction models.

Unfortunately, the XE website has only a single line that says anything about this cool feature of the database:

If you prefer R programming, Oracle Database supports that too.

How do we get started?

· 9 min read
Adrian Png

1280 720 Photo by Kaleidico

In a previous blog post, I provided a recipe for enforcing encryption for data-at-rest. However, it is important to remember that Transparent Data Encryption (TDE) protects your data from bad actors, who might have illegally gained access to the data files. Data exists unencrypted when loaded and accessed in the database. Malicious attacks can come from internal sources as well. When storing user confidential information, data stewards need to take greater responsibility in ensuring that data is only seen by staff who are authorized and require access to privileged information. That could mean creating tiered access and other protections like data redaction.

For example, an e-commerce website that handles credit card transactions, may require call centre staff to have access to the last four digits of a credit card number for verifying customers' identity when support call are received. Or an insurance claims website that requires claimants to submit their health identity number. The application displays the partially or fully-masked ID, enough to suggest that the data has been captured, but insufficient for a passerby to steal a glance.

Oracle's Data Redaction makes this possible, and with the latest release of Oracle Database 18c Express Edition, this technology is now acessible to developers for FREE! If you are new to Data Redaction, here's a simple recipe to get started!

· 3 min read
Adrian Png

1280 720 Photo by Kaleidico

Came across an interesting question on stackoverflow this morning, so I thought I'd give it a shot. Turned out to be an exciting morning of learning and a pretty lengthy response that I thought I'd cross-post to keep a copy for myself. If you have any suggestions for improvement, please feel free to contribute to the thread. Thanks!

· 6 min read
Adrian Png

1280 720 Photo by TheDigitalArtist

Transparent Data Encryption (TDE) is a powerful database feature that allows developers and administrators to very quickly, persist data encrypted at-rest. Applications do not require explicit coding to encrypt data for storage. TDE manages encryption during storage, and decryption when data is read, transparently and automatically.

TDE can also be used during backups. In the typical "tape falls off the back of the truck" scenario, malicious actors would not be able to decipher the data on the lost tapes without the crucial encryption key stored in the Oracle Wallet. Assuming of course, the wallet was not backed up to the same tape.

This enterprise-grade feature is found in the Oracle Advanced Security Database Option. That requires user to be licensed for database enterprise edition. However, with Oracle Database 18c Express Edition (18cXE), Oracle has made it free and accessible by everyone!

This post was specially written for Oracle Application Express (APEX) developers who are new to this technology. It provides a simple workflow for creating an encrypted tablespaces. Since APEX application live on the database, placing the parsing schema on a TDE-enabled tablespace, automatically protects the entire application. Hopefully this will encourage you to use it for protecting Personally Identifiable Information (PII) or any other sensitive and confidential data.

· 6 min read
Adrian Png

1280 720 Photo by Pexels

Updated November 17, 2018 Please read this follow-up post as well. It contains an important note about intermediate certificates and where to download the root certificates.

One of the difficulties working with web services in the previous version of Oracle Database Express Edition (XE) was the lack of a usable Oracle Wallet. This was fast becoming a huge problems for developers, as many API providers started enforcing requirements to that clients accessed services through secured channels. When working with 11g XE, I often relied on proxies within a sandbox to mask the need for SSL/TLS. I discussed this somewhat at length in a previous blog post.

With the 18c release, Oracle has opened up and provided us developers the opportunities to make our apps safer. We now have access to the (orapki) tools needed to manage an Oracle Wallet. I don't do this a lot, so I keep a snippets of what I do to make this easy.

· 5 min read
Adrian Png

1280 720 Photo by danny howe

What a prelude to Oracle OpenWorld 2018 (OOW18)! ICYMI, the next-generation Oracle Database 18c Express Edition (18cXE) was released late last week. The database software comes with a bunch of free enterprise features and options that usually come with a hefty price tag. Among them, Oracle Advanced Analytics, Oracle Advanced Security and Oracle RDF Semantic Graph are my favourites! See the Features Availability for more details on the Oracle great giveaway!

Since Saturday, I have been kept busy working on getting the development environment ready for Fabe. It was just weeks ago since we had the environment up and running on 11gXE. The instance is fully driven on the back of Docker that made it easier to swap out the database.

· 4 min read
Adrian Png

1280 720 The Great Wall of China

Authentication is an essential component of any enterprise application. These days though, it isn't enough to protect your applications with only a username and password. These are easily stolen by key loggers, sniffing non-encrypted HTTP traffic, phishing, hacked Internet services and the list goes on. To make matters worse, many users have a poor habit of reusing passwords that thieves then use to penetrate other systems. I have had my fair share of that experience.

The question is, can we implement two-factor authentication (TFA) in Oracle Application Express (APEX)?

· 2 min read
Adrian Png

Salmon Run - Honour the Dead

Here's my first contribution to the annual ODC Appreciation Day that was initiated by Tim Hall three years ago.

The product feature that I'd like to pay special tribute to this year is Oracle Multimedia (OMM). By now, I believe most would have heard that OMM has been deprecated. Mike Dietrich made special mention of it in a blog post earlier this year, with some clarity as to exactly when we can expect to see this useful feature go away for good.

· 3 min read
Adrian Png

Screenshot of application demonstrating Face Detection API

At Kscope17, I presented a session on various techniques for capturing and manipulating images. For those of who attend either in-person or the follow-up webinar, you might recall I briefly talked about image recognition and how they can be integrated with an Oracle Application Express solution using third-party JavaScript libraries.

· 9 min read
Adrian Png

Photo by John Michael Thompson

It's not unusual for an established principal investigator (PI) to have desks and shelves full of books, journal articles, grant applications, graduate student theses etc. Often, they'd also have a mountain of such material in electronic form and stored on desktop hard drives that sometimes get forgotten. It takes an extremely good memory to remember which articles are relevant to a particular keyword you might be interested in.

Thankfully, there's Oracle Text (and Oracle Application Express (APEX)) to keep you sane!

Setting the Stage

I'm not a researcher myself, so naturally, I don't horde a lot of these materials. For the purpose of this blog post, I will use open data made available, courtesy of the Canadian Government.

The National Sciences and Engineering Council of Canada (NSERC) is one of Canada's federal agencies that is responsible for promoting and providing funding for research in the natural sciences and engineering fields. They publish annual lists of grants that were successfully awarded. You can find them here. I used only one year's (2017) worth of data. There's a lot of research activity going on in the north!

Start off by first creating the necessary data structure to hold the data. I used the Quick SQL tool to quickly generate the necessary DDL script and then create the database.

Quick SQL Shorthand

# settings = { genPK: false, db: 11g }
award
id number /pk
name varchar2(200)
department varchar2(200)
organization_id number
institution varchar2(200)
province varchar2(100)
country varchar2(100)
fiscal_year number
competition_year number
award_amount number
program_id varchar2(50)
program_name varchar2(200)
program_group varchar2(100)
committee_code number
committee_name varchar2(200)
area_of_application_code number
area_of_application_group varchar2(200)
area_of_application varchar2(200)
research_subject_code number
research_subject_group varchar2(200)
research_subject varchar2(200)
application_title varchar2(500)
source varchar2(50)
application_summary varchar2(4000)

Oracle SQL Output

create table award (
id number not null constraint award_id_pk primary key,
name varchar2(200),
department varchar2(200),
organization_id number,
institution varchar2(200),
province varchar2(100),
country varchar2(100),
fiscal_year number,
competition_year number,
award_amount number,
program_id varchar2(50),
program_name varchar2(200),
program_group varchar2(100),
committee_code number,
committee_name varchar2(200),
area_of_application_code number,
area_of_application_group varchar2(200),
area_of_application varchar2(200),
research_subject_code number,
research_subject_group varchar2(200),
research_subject varchar2(200),
application_title varchar2(500),
source varchar2(50),
application_summary varchar2(4000)
)
;

NOTE Change the DB version as required. The default is 12c, but since I am running the database Express Edition (XE), I used 11g.

Filling Up the Repository

The data from NSERC is fairly clean and formatted. The 2017 data that I downloaded however, was a little too large to import using the built-in SQL Workshop tool in APEX. Fortunately, SQL Developer has a rather sophisticated functionality for doing the same, though it wasn't without challenges. I'll leave data importing methods for another day's discussion. For the purpose of this exercise, I did managed to import at least 5,000 records.

Search-Enabling the Table of Grants Awarded

Oracle Text is a very powerful, no-cost feature of the Oracle Database that doesn't get enough attention as it should. There are many interesting concepts and customizations that can be applied and tune the Oracle Text Index. However, creating a basic index is rather straightforward and should not be feared.

My goal:

  • List grant awards that are about fossil fuels and the effects on the environment.
  • The corpus contains applications written in both English and French. Users should be able to search with either language.
  • Users enter search terms using only one text field, but search must include the following columns:
    • application_title
    • application_summary
    • research_subject
    • area_of_application

Required Database Privileges

To work with Oracle Text, the database user requires a few additional privileges that have already been granted to the role CTXAPP. Assigning the user to the role should be all that's needed:

grant ctxapp to dbuser;

Create the Oracle Text Index.

begin
-- Remove any previously created DATASTORE preference with the same name.
begin
ctx_ddl.drop_preference('SEARCH_DATASTORE');
exception
when others then null;
end;

-- Create the DATASTORE preference.
ctx_ddl.create_preference('SEARCH_DATASTORE', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('SEARCH_DATASTORE', 'COLUMNS', 'application_title, application_summary, research_subject, area_of_application');

-- Drop any existing index with the same name.
begin
execute immediate 'drop index award_idx';
exception
when others then null;
end;

-- Create the index.
execute immediate q'[
create index award_idx on award(application_title)
indextype is ctxsys.context
parameters (q'{
DATASTORE SEARCH_DATASTORE
}')
]';
end;
/

The comments in the procedure shown above explains the basic commands needed to create the index. For convenience, they are listed below:

  1. Remove any previously created preference with the same name.
  2. Create the DATASTORE preference.
  3. Drop any existing index with the same name.
  4. Create the index.

As mentioned, there are additional indexing elements that could be used to further optimise and customize the search index, but they are out of scope at this time. This simple setup will already allow some powerful search abilities with minimal query design.

Query the Data

There are different operators to use in the WHERE clause, depending on the index type created. In the example, an index of type CONTEXT was created, thus the query will have to be constructed using the CONTAINS operator.

select
id
, application_title
, application_summary
from award
where 1 = 1
and contains(application_title, '"fossil fuels" and environment', 1) > 0
order by search_score desc;

Oracle provides a long list of CONTAINS query operators that allows users to formulate powerful queries. The example above is a simple boolean search (AND operator) requiring both the phrase "fossil fuels" and word "environment" for a match. A simple misspelling of "environment" would likely yield no results. That's where other operators like fuzzy provide a margin error, allowing returning results even when there are no exact matches. The same operator might even forgive simple nuances like "colour" versus "color". There are also wildcard (%) and other operators that are denoted using special characters that could sometimes spell trouble even for a seasoned Oracle Text user.

To protect the untrained user, it is recommended to pre-process the user input and restructure keywords and options into proper query operator constructs. This isn't always easy, and so, we are fortunate that Roger Ford, Oracle Text's Product Manager, posted a blog and useful utility package for parsing user inputs from a simple text field. The syntax closely resembles Google's search operators and should be familiar with most users. The code is available to download at the end of Ford's article.

After compiling the script, functions such as parser.simpleSearch and parser.andSearch will be available to use. Check the comments in the code for details about each function.

SQL> select parser.andsearch('"fossil fuels" environment') from dual;
-- returns ({fossil fuels}&{environment})

Querying with APEX

With too little screen space on a blog post, it's easier to demonstrate the search results using APEX. The accompanying demo application showcases Oracle Text integration using two different user interface approaches.

The first uses Interactive Grids (IG) that has built-in support for Oracle Text. After creating the IG, click on Attributes and then on the right, scroll down to the Advanced section and select the indexed column as the Oracle Text Index Column attribute.

Setup Oracle Text for Interactive Grids

Interactive Grids allows developers to specify a PL/SQL function for converting simple keyword inputs to well-formed Oracle Text syntax. Go to the application's Shared Components> Component Settings > Interactive Grids and then enter the name of the function to use. We can use one of the functions from Ford's Parser package as they meet the requirement of accepting a single string argument.

Specify a PL/SQL Function for Preprocessing Input String

The second approach uses a Classic Report (CR) that allows more flexibility on how the results are displayed, for example, sorting the results by score (descending) and application title (ascending). The user enters the search keywords in a single text field page item (P2_SEARCH_TERMS) that is then parsed and used with a CONTAINS operator to query the table. The SQL query used is as follows:

select
application_title as search_title
, application_summary as search_desc
, round(score(1), 2) as value_01
, 'Score' as label_01
, q'[javascript:apex.message.alert('Not implemented.');void(0);]' as search_link
from award
where 1 = 1
and contains(application_title, parser.progrelax(:P2_SEARCH_TERMS), 1) > 0

Displaying Oracle Text Search Results Using Classic Reports

Both search interfaces will accept either English or French keywords. However, search results are language-specific. If an English keyword is entered, then only applications submitted in English will be returned. Likewise with French search terms. For a heterogenous search, a thesaurus will be required. The parsing function will also need to be "translation-aware", and either accept or transform queries to use operators such as TR. These translation operators will expand the search keywords to include the specified language's equivalent term.

The ctxload utility provides developers the ability to import a thesaurus contained in a plain-text file. Developers can also script the thesaurus:

declare
l_thes_name varchar2(11) := 'SCIENG_THES';
begin
ctx_thes.create_thesaurus(name => l_thes_name);
ctx_thes.create_phrase(tname => l_thes_name, phrase => 'water');
ctx_thes.create_phrase(tname => l_thes_name, phrase => 'waters');
ctx_thes.create_translation(tname => l_thes_name, phrase => 'water', language => 'FRENCH', translation => 'eau');
ctx_thes.create_translation(tname => l_thes_name, phrase => 'waters', language => 'FRENCH', translation => 'eaux');
end;
/

Unfortunately, Ford's parser library does not support translations, but here's an example query that could be used to demonstrate the translation capability. The search term is "water", and the results will return both English and French applications containing either "water" or "eau":

select
id
, score(1) as search_score
, application_title
, application_summary
from award
where 1 = 1
and contains(application_title, 'tr(water, french, scieng_thes)', 1) > 0
order by search_score desc;

Final Notes

Besides searching large bodies of text, Oracle Text has the ability to index and search binary files such as PDF, HTML, XML and even Microsoft Word documents. Check out a full list of supported document formats.

NOTE: If the document filtering appears not to be working , i.e. a query does not return any expected results, then check that the needed operating system libraries, e.g. compat-libstdc++-33, have been installed. You can test this by running the binary: $ORACLE_HOME/ctx/bin/ctxhx.

Get more background about this thread on the Oracle community forums.

All the technology described and used in this blog post and demo are available to at no cost. That's always a welcomed surprise for any research project.

Software Used

SoftwareCost
Oracle Database 11gR2 XEFREE
Oracle APEX 18.1FREE
SQL Developer 18.2FREE

· 14 min read
Adrian Png

In a flash, it has been almost three years since I left the Centre for Heart Lung Innovation, so it's no surprise that a recent tweet caught my eye and tugged the memory chord. I was introduced to Oracle Application Express (APEX) on August 26, 2010. A year (and a major release) later, I had the opportunity to demonstrate it's capabilities with proof that it could help improve productivity in our research centre. As a sidenote, back then, attendees receive DVDs full of developer software goodies. APEX however, didn't need much, just check out the hands-on lab requirements below.

· 4 min read
Adrian Png

Floppy Disks

ICYMI (in case you missed it), a team from Oracle has been hard at work these few months churning out a bunch of Vagrant configuration files and scripts that help you provision some really cool virtual machines (VM), powered by Oracle Enterprise Linux. At the time of writing, here are some of the cool VMs you can build:

· 7 min read
Adrian Png

Where Dreams CAME True

That's right! Here's the customary post-Kscope article. This year though, is one Kscope to remember! Along with other Oracle Application Express (APEX) community members, I had the surprise honour of joining the ranks of the Oracle ACE Program. And so I wanted to start off by first thanking newly-minted Oracle ACE Director Opal Alapat. Last year, I had deep suspicions that she was responsible for my ODTUG Volunteer Award. Then came the ACE Associate nomination.

Opal, if you are reading this: THANK YOU!