Search

Top 60 Oracle Blogs

Recent comments

SQL

Video : Hybrid Partitioned Tables in Oracle Database 19c

In today’s video we’ll give a quick demonstration of Hybrid Partitioned Tables, introduced in Oracle Database 19c.

The video is based on this 19c article.

The video only has a single example using external partitions pointing to CSV data. The article also includes and example using a Data Pump file.

Video : Liquibase : Deploying Oracle Application Express (APEX) Applications

Today’s video is a quick demonstration of deploying an Oracle Application Express (APEX) application using Liquibase.

The video is based on a new article of the same name, which covers the deployment of both APEX workspaces and APEX applications using Liquibase.

Here’s some other content you might find useful.

Video : APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns

Today’s video is a quick demonstration of using the APEX_DATA_PARSER package to convert simple CSV, JSON, XML and XLSX data into rows and columns.

If you want the copy/paste examples and the test files, you can get them from this article.

Yet another reason why you should always install APEX in your databases.

The myth of NoSQL (vs. RDBMS) agility: adding attributes

By Franck Pachot

.
There are good reasons for NoSQL and semi-structured databases. And there are also many mistakes and myths. If people move from RDBMS to NoSQL because of wrong reasons, they will have a bad experience and this finally deserves NoSQL reputation. Those myths were settled by some database newbies who didn’t learn SQL and relational databases. And, rather than learning the basics of data modeling, and capabilities of SQL for data sets processing, they thought they had invented the next generation of persistence… when they actually came back to what was there before the invention of RDBMS: a hierarchical semi-structured data model. And now encountering the same problem that the relational database solved 40 years ago. This blog post is about one of those myths.

Video : SQLcl and Liquibase : Automating Your SQL and PL/SQL Deployments

In today‘s video we’ll give a quick demonstration of applying changes to the database using the Liquibase implementation in SQLcl.

The video is based on this article.

You might also find these useful. The secure external password store is a good way to make connections with SQLcl. If you support a variety of database engines, you may prefer to use the regular Liquibase client.

Refactoring procedural to SQL – an example with MySQL Sakila

By Franck Pachot

What I want to show in this blog post is that, as in mathematics where you have to apply some algebra rules to transform an equation to an equivalent one, the database developer must translate the business specification to an equivalent that is optimized (in performance, reliability and readability) for the data model.

Oracle 20c SQL Macros: a scalar example to join agility and performance

By Franck Pachot

.
Let’s say you have a PEOPLE table with FIRST_NAME and LAST_NAME and you want, in many places of your application, to display the full name. Usually my name will be displayed as ‘Franck Pachot’ and I can simply add a virtual column to my table, or view, as: initcap(FIRST_NAME)||’ ‘||initcap(LAST_NAME). Those are simple SQL functions. No need for procedural code there, right? But, one day, the business will come with new requirements. In some countries (I’ve heard about Hungary but there are others), my name may be displayed with last name… first, like: ‘Pachot Franck’. And in some context, it may have a comma like: ‘Pachot, Franck’.

There comes a religious debate between Dev and Ops:

GROUP BY might be distinctly better than DISTINCT

One of the cool things with materialised* views in Oracle is their ability to be kept in sync with the source table(s) from which they are derived from, in real time or near real time. To achieve this, we typically employ mechanisms such as materialised view logs to capture modifications to the source tables, and occasionally we need to change the definition of the materialised view itself when dealing with aggregations and joins. However, some times we know that if DML on the source is incredibly rare and/or the cost of updating the materialised view is very small, we can avoid all that and simply perform a REFRESH COMPLETE whenever a transaction is committed on the source tables. This avoids any issue around the materialised view becoming stale, and also avoids the need for scheduler jobs to keep the materialised view refreshed.