POUG 2020

The Oracle Database is more than “just” a database. One of the many extra things is Machine Learning capabilities, avoiding users to export data, which can be a security concern, and having to learn various other languages like Python or R. The database comes with some packages enabling ML algorithms, easily accessible by the same unique language as everything else in the database: SQL. It’s really easy to get started and setup, train and use a ML model in the database. Almost as easy a “next, next, done” approach. When something is as simple as that, you often trade complexity with accuracy: it’s simple and therefore less accurate. A not accurate ML model isn’t very useful as the target is the best accuracy ever for predictions and classifications.

This talk takes a unique dataset and performs ML tasks with Python (and a notebook) and in the database. The target is to compare the complexity of both approaches and the achieved accuracy, proving (or not) if ML in the database is really something worth looking at, without having to learn Python or something else.

A “live” demo will be used to show the simplicity of one solution compared to the other, as well as the accuracy.

Imagine doing a SELECT and the answer coming back is “approximately 42, give or take a little.” Normally you wouldn’t want that to happen, you expect a SELECT to give you an accurate answer every time. But building a sales dashboard for your boss, for instance, could be a choice between showing him “we had approximately 4200 unique visitors from France last week” in a quarter of a second, versus showing him “we had exactly 4189 unique visitors from France last week” in 5 seconds. Most likely your boss would be happier with a fast but “good enough” answer.

Oracle supports several Approximate Query Processing Functions, starting with APPROX_COUNT_DISTINCT in version 12.1, adding APPROX_MEDIAN and APPROX_PERCENTILE as well as the capability of doing approximate aggregations on multiple levels in version 12.2, followed by APPROX_RANK, APPROX_COUNT and APPROX_SUM in version 18.1. These functions allow you to write SQL that gets approximate, but fast, answers.

But what if you already have a dashboard application built using good old trusted SQL giving accurate answers, but it is really slightly unsatisfactory as each click takes several seconds to complete? Then you can do a FAST=TRUE - if you are prepared to accept approximate answers. You can do an ALTER SESSION that enables the optimizer to rewrite for example your COUNT(DISTINCT column) to an APPROX_COUNT_DISTINCT(column) automatically. Then your application will give approximate, but fast, answers without needing change.

This presentation discusses the different approximate functions, the approximate aggregations and the ALTER SESSION method of speeding the queries - showing examples of syntax and use cases.

I will cover most of new features of Oracle Database 20c

Partitioning will notably help you to store and “manage” large tables of facts or items. I expect Partitioning to be or to become an effective tool to store very large, and often volatile sets of data in a “relational” database and I will point out some obvious use-cases.

After the presentation, the attendee will be prepared to use Partitioning, will understand some of the mistakes from the past, will be able to make better design-decisions for large tables, and is better able to achieve success.

Today, the choice of the database technology is in the hands of the developers, and Open Source alternatives look appealing. Many talks and papers show the growing features of MySQL or PostgreSQL, which compete with the commercial RDBMS. But do not forget the core features that are there for decades. They may not be there, or less efficient, in those alternative solutions. This presentation shows those basic features, with demos: in-place updates, cursor sharing, guaranteed disk writes, advanced materialized views, IOT, wait events, online operations, etc. Many were already in Oracle 30 years ago and users take them for granted. You may not realize what you will miss when going to another RDBMS.

More and more companies and organizations migrate from Oracle, Informix, IBM DB2, MS SQL, EDB, Maria DB or MySQL to PostgreSQL. The reasons for this are multiple: On the one hand, there are many companies that refuse to pay high license fees, which are common for commercial closed source providers. On the other hand, there are more and more companies that migrate to PostgreSQL for technical reasons.
PostgreSQL is the most advanced open source database system on the market and has been firmly established for many years – it has been voted Database System of the Year several times. PostgreSQL is highly reliable, stable, scalable and secure, developed by a large global community.

During this talk you will learn more about all the aspects of moving from Oracle to PostgreSQL.

Java people have been using unit tests for ages; PL/SQL people … not necessarily. We used dbUnit tests (maybe not in the intended way), which helped us a little, but code quality still left room for improvement. So something had to happen. Thanks to the great open source community we didn’t have to look far: The new version of utPLSQL has been around for a while, and it finally reached our project last year.

Learn about - the easy installation - the obstacles we had to overcome - the first steps and errors we made - where we found support and - how utPLSQL helped us to become proper PL/SQL unit testers!

Using bulk operations to do dml from plsql is a lot faster than processing row by row.
However, during bulk dml some or all records may not be successfully created, modified or deleted.
This presentation will be about some of the pitfalls in handling these errors using "save exception" and its alternative "log errors".
Why is error_index a misleading name for that column in sql%bulk_exceptions? And how do you overcome the implications?
What if a record fails halfway through multiple consecutive bulk operations on relationally dependent tables?
Can log_errors be used as an alternative to using "save exceptions"?
You will be given the answers to these and more questions and gain a better understanding of how to deal with errors in bulk operations.

Oracle Text is included with every edition of the Oracle database - even the free Express Edition (XE). Many APEX applications can benefit from adding its powerful search capabilities. This presentation will explain how to quickly add powerful text searching capabilities to your APEX and PL/SQL applications.

We’ll look at: - Strategies for gathering and indexing textual data to be searched - Indexing of BLOBs containing PDFs or Word documents - Indexing of structured documents such as XML or JSON - Searching your data using the Oracle Text syntax - Different search types such as word, phrase, stem, etc. - Searching within tags - Google-like highlighting of snippets in search results

ORDS, the Oracle Rest Data Services, has evolved from just a supporting component of Oracle Application Express to the de facto standard tool for delivering RESTful API services for the Oracle database. In this session we will cover - what is Oracle Rest Data Services
- set up Oracle Rest Data Services to talk to your database - use Oracle Rest Data Services to get data of all sorts in and out of your database - create documentation for your API’s - secure the calls through Oracle Rest Data Services

In the end it will be clear, it is not magic. It is the best way to get even more value out of your Oracle database!

This presentation will cover all the necessary steps to open up the information in your database to the outside world - of course in a secure way. ORDS nowadays is so sophisticated that most steps are extremely easy to do. It almost looks like magic! There will be demo’s using SQL, PL/SQL and JavaScript.