I have been struggling in my first simple steps with querying data in a NoSQL Database table on Oracle Cloud. Creating the table is easy enough – either in the console or through Terraform or the CLI. However, the data that my Node application creates in the table seemed very […]
sql
Preparation for migrating data to Oracle Virtual Private Database
preparation for migrating data to Oracle Virtual Private Database Introduction Recently I was part of a team involved in the preparation of migration data belonging to multiple business units into a single Oracle 19c database with Virtual Private Database (VPD). The VPD solution is used for the virtual separation […]
How to unit test a SQL query?
A simple challenge: I am working on a SQL query. Not a trivial query. I am trying various approaches. I believe I may have found a solution that works. That produces the correct solution. How can I be sure? How can I try to further refine the query while maintaining […]
SQL–Only Counting Records Sufficiently Spaced apart using Analytics with Windowing Clause and Anti Join
A nice SQL challenge was presented to me by a colleague. The challenges basically consisted of this table. A table contains records that describe login events. Each record has a login timestamp and the identifier of the person logging in. The challenge is to count “unique” login events. These have […]
Install SQLcl on Oracle Cloud Infrastructure Cloud Shell and make it work against an Autonomous Database
Cloud Shell on Oracle Cloud Infrastructure is a versatile management and development environment to access and manage Oracle Cloud Infrastructure resources. Cloud Shell comes with many tools and runtime out of the box (or rather inside the box) including OCI CLI, Java and SQL*Plus. However, the Oracle SQL Developer Command […]
Getting started with NoSQL Database Service on Oracle Cloud Infrastructure
This morning I discovered a new entry in the menu on my Oracle Cloud Infrastructure Tenancy (on Ashburn, USA): the NoSQL Database service had been added overnight. The service offers a key-(schema-less) document store. It also supports ACID transactions, SQL on JSON documents, joins across tables, structured tables and if […]
Introduction to Oracle Machine Learning – SQL Notebooks on top of Oracle Cloud Always Free Autonomous Data Warehouse
One of the relatively new features available with Oracle Autonomous Data Warehouse is Oracle Machine Learning Notebook. The description on Oracle’s tutorial site states: “An Oracle Machine Learning notebook is a web-based interface for data analysis, data discovery, and data visualization.” If you are familiar with Jupyter Notebooks (often Python […]
Building a Conference Session Agenda with Oracle APEX – notes on Pivot, Modal Popup and jQuery
AMIS is part of the Conclusion ecosystem of over 20 companies, each with their own specialty and identity. Several times per year, we organize Conclusion on Stage – a conference that spans across the ecosystem. Presenters from most companies under the Conclusion umbrella submit session proposals. Close to 30 sessions […]
Some neat APEX tricks while building a Session Like application for our Conference
AMIS is part of the Conclusion ecosystem of over 20 companies, each with their own specialty and identity. Several times per year, we organize Conclusion on Stage – a conference that spans across the ecosystem. Presenters from most companies under the Conclusion umbrella submit session proposals. Close to 30 sessions […]
Some Notes on my small steps with Oracle APEX
In this article, I will share some of my personal findings and discoveries as I start out building a small APEX application. No experienced developer is likely to learn anything from this article – but first timers like me could perhaps benefit from my findings. Some of the challenges I […]
Quickly developing and hosting your friendly neighbourhood application – for free using … Oracle APEX
My challenge: my wife wants to set up a system for some of our friends and relatives to let each other know about appliances and things they can share as well as objects they offer up [because they plan to get rid of them]. The specifications are fairly straightforward: users […]
Building a Conference Session Recommendation engine using Neo4J Graph Database
This article describes a use case for which a traditional SQL-powered relational database approach can provide a solution – but for which that traditional approach is not the optimal solution. SQL is jack of all trades – you can make it do almost anything you need. And therefore it is […]
Querying and Publishing Kafka Events from Oracle Database SQL and PL/SQL
One of the session at CodeOne 2018 discussed an upcoming feature for Oracle Database – supported in Release 12.2 and up – that would allow developers to consume Kafka events directly from SQL and PL/SQL and – at a late stage – also publish events from within the database straight […]
Rapid generation of Oracle DDL scripts for Tables, PL/SQL APIs, Sample Data
Yesterday, at the Oracle ACE Directors Product Briefing, I received a gift. It is called QuickSQL. And it is a free online service that generates SQL DDL and DML scripts. The gift in this case was the knowledge about this service – I was not aware of it. Go to […]
Introducing Elastic Search NoSQL to Oracle SQL developers – comparing dozens of ElasticSearch and SQL operations (a bit like Rosetta)
Even for organizations with strong roots in relational databases such as Oracle RDBMS, there may be valuable opportunities for leveraging additional data sources, for example to support special (search) use cases. Elastic Search (Index) is one of those data stores that can add value – for example to provide powerfur […]
Oracle REST Data Services (ORDS)
While preparing for writing an article about “Oracle API Platform Cloud Service” I wanted to quickly be able to generate example JSON payload’s based on some tables in the “HR’ schema. To achieve this, I came across “Oracle REST Data Services (ORDS)” and wanted to know more about it. In […]
Microservices and Updating Data Bound Context on Oracle Cloud with Application Container and Event Hub (plus DBaaS and MongoDB)–Part Two
This article describes – in two installments – how events are used to communicate a change in a data record owned by the Customer microservice to consumers such as the Order microservice that has some details about the modified customer in its bound context. The first installment described the implementation […]
Machine Learning in Oracle Database – Classification of Conference Abstracts based on Text Analysis
Machine Learning is hot. The ability to have an automated system predict, classify, recommend and even decide based on models derived from past experience is quite attractive. And with the number of obvious applications of machine learning – Netflix and Amazon recommendations, intelligent chat bots, license plate recognition in parking […]
after 27+ years in Oracle land I am forced to patch sqlplus
When starting to use sqlplus 12.2 I noticed that my SQL prompt was not changing to what login.sql told it to be. This did not happen in sqlplus 12.1 or lower versions. Maybe this is a bug, maybe a new feature I thought. Behaviour in 12.2 of sqlplus indeed has […]
Oracle Database standard Geo Location Support using Locator (included in every edition!)
Many databases have native support for locations en geodata – and determining distance and closest locations (within a certain perimeter). Oracle Database has the [Graph and] Spatial Option – that supports even the most advanced and exotic forms of location related data querying (including multidimensional shapes and probably relativistic effects); […]
The normalization of Big Data – reporting from Oracle OpenWorld 2016 on Big Data and Data Integration
The importance of data has never been in doubt in the world of Oracle. Through machine learning and predictive analytics as well as real-time streaming data and Big Data, the data spectrum has broadened considerably. With the quickly expanding range of storage and processing facilities, analysis algorithms and visualization means, […]
The state of the flagship as per Oracle OpenWorld 2016 – Oracle Database 12c Release 2, Exadata Express Cloud Service, SQL and PL/SQL
The new 12cR2 release of Oracle Database had already been announced and discussed at length over the past year. And now it is finally available. And only as cloud service for now. Initially through the Exadata Express Cloud Service – which consists of a single PDB in a multitenant CDB, […]
Oracle SQL Pattern Recognition – introducing the Match Recognize operator
Oracle Database Release 12c (12.1) introduced a new operator that we can use in SQL queries. The MATCH_RECOGNIZE operator allows us to detect patterns in our relational data. Specifically: it allows us to identify records that mark the beginning of a set of records that together form a pattern. In […]
Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE
One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various […]
AMIS25 Beyond the Horizon Conference – Session Resource Repository
Below you will find an overview of all the sessions that took place at the AMIS25 Beyond the Horizon conference (1-3 June 2016, Katwijk, The Netherlands) along with the associated session resources – such as the presentation slides and where applicable the source code. Presenter(s) Title Session Resources Aino […]
Parse JSON Array in SQL and PL/SQL – turn to a Nested Table
Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON. […]
Reflections after Oracle OpenWorld 2015 – Business Analytics (Big Data, GoldenGate, OBI (EE), ODI, NoSQL)
note: I would like to thank Mark Rittman of RittmanMead for sharing many of this findings from Oracle OpenWorld 2015 as well as a comprehensive slide desk. Business Analytics covers the areas of Business Intelligence, Data Discovery and Big Data as well as some of the data gathering and preparation […]
MySQL 5.7 – more of a database than you might have guessed
One of the cool stories of Oracle OpenWorld and JavaOne 2015 is the story of MySQL, in particular of release 5.7 of MySQL. For me, working primarily with Oracle Fusion Middleware, it is easy to overlook MySQL. However, MySQL is a very serious database platform that powers not only some […]
Reflections after Oracle OpenWorld 2015 – Data Management (Database)
This article gives an overview of some of the most eye catching stories from Oracle OpenWorld 2015 around Data Management. It discusses on premises products such as Oracle Database as well as cloud services around the Database. Data Management in the Oracle Integrated Cloud Platform Services composition covers Big Data, […]
Time is of the essence – The Fourth Dimension in Oracle Database 12c (on Flashback and Temporal Database)
Time has always been an important dimension for data in any database with topics like when was data created, when are records valid, how did records evolve over time, can we compare with yesteryear or even travel through time and data. The Oracle Database 12c release added a number of […]
Using an aggregation function to query a JSON-string straight from SQL
Last week I read this blogpost by Scott Wesley. In this post he describes that he uses a custom aggregate function to create large JSON-strings. And for that he used a solution as described in this post by Carsten Czarski. That post of Scott reminded me of a post by […]
Set values for bind parameters in a database view using application context – single round-trip context manipulation and querying
While writing an article on database session context for the OTech Magazine’s Spring issue, I tried out whether I can send a SQL statement from a database consumer such as the SOA Suite Database Adapter or an ADF BC ViewObject that passes in the values for some bind parameters that […]
Oracle OpenWorld 2014: Non-life altering observations (but still interesting and useful)
This article does not necessarily discuss the big themes and major stories of Oracle OpenWorld 2014. It does mention a number of facts that I discovered, overheard, observed or otherwise found out about during last week’s conference. They are not necessarily from formal Oracle sources, they have have been part […]
Everything That Is Really Useful in Oracle Database 12c for Application Developers – Oracle OpenWorld 2014
A few days ago, I presented a session at Oracle OpenWorld 2014 about the most useful features for application developers in Oracle Database 12c (12.1.0.1 and 12.1.0.2). The main topics I demonstrated were JSON, Flashback, Temporal Validity, Match Recognize (pattern matching), In Line PL/SQL and the SQL Translation (Profiles) Framework. […]
SQL: combine inline PL/SQL function with inline view in Oracle Database 12c SQL Query
Oracle Database 7.3 was the first to support inline views – using the select * from (select * from) syntax. Oracle Database 9i added the with clause for subquery factoring, using a syntax like with vw as (select * from) , vw2 as (select * from vw) select * from […]
SQL Challenge: Drilling down into World Cup Football Tag Cloud
In a previous article, I have introduced the World Cup Football tag cloud (https://technology.amis.nl/2014/06/22/sql-challenge-dynamically-producing-a-tag-cloud-for-world-cup-football-matches/). I have shown how tags can be associated with football matches, both explicitly and implicitly – the latter derived automatically, based on the structured information about the matches. The tag cloud can be used as a […]
SQL Challenge: Dynamically producing a tag cloud for World Cup Football matches
Tag clouds are a special way to present information about a set of records. Tags are descriptive labels that are associated with specific records. A tag cloud visualizes the various labels that have been applied as well as the relative occurrence frequency of these labels. The tag cloud can be […]
SQL Challenge: Find World Cup Football matches with a comeback
As I recently said in another blog post, the World Cup Football is such a wonderful source of data that begs to be digested using SQL in many different ways. While working on a tag cloud producer – topic for a subsequent article – I ran into a special challenge. […]
SQL Challenge – World Cup Football 2014 – Retrieving matches and Calculating Group standings
Few things lend itself so well to SQL explorations as sport related data. The imminent World Cup Football (or Soccer in some deprived parts of the world)2014 in Brazil is no exception. In this article, I will show and example of how to query the results for the matches played […]
SQL Challenge – packing time intervals and merging valid time periods
During the OUGF 2014 Harmony Conference in Finland, I attended a seminar by Chris Date on Temporal Data and the Relational Model. He introduced an approach within the relational model to describe temporal data and deal with (temporal) intervals and suggested how such could be included in the SQL language. […]