The core Formula One data could be represented in a graph like this: Drivers compete in races that are hosted on circuits. A result is achieved by the driver (position and points for the championship). If this is the graph, some questions can easily be answered: With whom has a […]
SQL
Oracle Database 23c as Graph Database–SQL Property Graph for network style querying
I bet you are used to relational data structures that you query using SQL. And so do I. And there is nothing in terms of data that a good SQL query cannot answer. OK, the query can become quite long – with inline expressions and multiple joins – but it […]
Get Going with SQL Developer against Oracle Database 23c Free
In an earlier article, I showed how to quickly get going with an Oracle Database 23c Free instance in a Gitpod workspace – cloud based, ephemeral, quick start, zero install environment. As part of that workspace, you have access to both SQL*Plus and SQLcl as command line interface to work […]
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 […]
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 […]
Better track the Usage of Database Options and Management Packs, or it will cost you
So here it is Oracle announces a license audit, some urgency kicks in and this familiar but also really serious question comes down from management: “Are we using any unlicensed database features“. The seriousness is quite understandable, because if so, the company can look forward to some negotiations with Oracle […]
Oracle 12c STIG Password Generator in PL/SQL
Creating or modifying an Oracle Database user password can be done by using any standard password generator you can find on the WEB. But I wanted a password to comply to “ora12c_strong_verify_function”, and this isn’t as easy to generate as you might expect. Though most generators provide options to include […]
One of the many nice new features in 12c database: code based access control
Topic of this blog is a nice new feature in 12c, not the plsql package I built that’s using it. So here’s the story.. For one of our customers we needed to have a simple schema comparison tool that would be able to check, as part of application deployment activity, […]
Virtual columns
Maarten wrote a post on Virtual Columns in the oracle database. I read a blogpost on preventing people issuing SELECT * on a table. This was done in a different database, so I decided to try it out in my Oracle Database. First I create a simple table: and I […]
Virtual Private Database…
Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how […]
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 […]
How-to set the current database schema of an application using a global context
Question: We have customers who want to work with different database schemas and we want to determine dynamically – depending on a choice during or after login – which database schema the application in a given session should use. Is it possible to set this up in the database? Answer: […]
SQL> Select * From Alert_XML_Errors;
Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying timestamps from within view Alert_XML_Errors. Like this, with the help of 2 types and a pipelined function. And checkout the errors […]
SQL> Select * From Alert_XML;
By mapping an external table to some text file, you can view the file contents as if it were data in a database table. External tables are available since Oracle 9i Database, and from Oracle 11gR2 Database on, it is even possible to do some inline preprocessing on the file. […]
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. […]
SQL Challenge – Calculating the legal maximum speed measured by the laser speed gun
The speed of cars can be detected at large distance and with great accuracy using laser speed guns. How the measured speeds translate into speeding tickets depends on the legislation around speeding and of course the maximum allowed speed for cars. Most laws state that the maximum speed for which […]
Select a blob across a database link, without getting ORA-22992
Just a quick blog about a simple trick to select a blob across a database link, especially for a collegue of mine, Harry Dragstra. Say, you have a table with a blob on a remote database When you use a normal select statement to get all columns you run into […]
SQL Inside Out – Challenging Puzzles and Puzzling Challenges – Seminar 22nd April, Sweden
April is the month of the presentations and seminars for me (May and June too by the way). On Wednesday April 22nd, I will be presenting a seminar in Sweden (in Kista, near Stockolm), titled: SQL Inside Out – Challenging Puzzles and Puzzling Challenges. It is an interesting journey […]
Have MERGE remove records from Target that are not in the Source – Oracle 10g
The Oracle 10g Database release saw an extension of the MERGE statement with a DELETE clause. That by the way makes MERGE the only statement that can fire all three Statement Level triggers: Insert, Update, Delete. And it will make Oracle glad that back in 2003 when it discussed the […]
Analytical SQL Functions – theory and examples – Part 2 on the Order By and Windowing Clauses
To understand how Analytical Functions are used in SQL statements, you really have to look at the four different parts of an Analytical ‘clause’: the analytical function, for example AVG, LEAD, PERCENTILE_RANK the partitioning clause, for example PARTITION BY job or PARTITION BY dept, job the order by clause, for […]