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 […]
collection
Oracle Database 12c: joining and outer joining with collections
Joining is a key ingredient in most SQL queries. Using collections (aka nested tables) that are produced on the fly inside the SQL query or that are returned by a PL/SQL function that is invoked from a TABLE operator in the query is a powerful weapon as well. Oracle Database […]
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)
This presentation will hopefully convince database developers that types in the Oracle Database are worth their salt – and more. With the recent improvements in 11gR2, the pieces are available to complete the puzzle of structured and modern programming with a touch of OO and more importantly to create a […]
Retrieving the values for domains in SQL queries in the Oracle RDBMS – months, days, numbers and custom value sets
It happens quite frequently that in our database applications – in queries or in PL/SQL packages – we need to use the values from more or less standard domains or custom lists of values. For example the list of all months in the year – to show a breakdown list […]
Extreme Decoupling in SQL – Select and DML against a table that is not a table at all
One of the key objectives – though not ulterior motives – in Service Oriented Architecture is decoupling. By decoupling currently mutually dependent agents, we introduce more opportunity for flexibility, no-impact optimization, reuse and interoperability. This does not require Web Services and the world of WS* to realize. Even with fairly […]
Tag Cloud analysis in SQL and PL/SQL – Using Collections, Table Operator, Multiset and Collect operator
Another world shattering topic: analyzing tag clouds. This article describes a very simple first step for some analysis at database level of tags that have been associated with database records this analysis can be the foundation for presenting the tag cloud in the user interface that enables the user to […]
Analyzing Match Scores in ‘fuzzy searches’ – explaining the shades of gray in a non-binary world
In a previous article – Not all has to be black and white in SQL Queries – , I discuss how not all searches are about perfect matches. The search for your lifelong partner, or your next job are fine examples of searches where a perfect match is hard to […]
Oracle & JDBC – Passing a ‘table of custom object type’ to a stored procedure – implementing efficient single round trip data exchange (part two of an ADF application on a PL/SQL API)
One very efficient way of passing data from the Java application to the database is through the use of custom Oracle Types. Even when we want to send a heterogeneous data set, for example a data set with master and details records, custom type based objects can provide a powerful […]
Juggling with SQL Types in SQL and PL/SQL – fading borders between Collection and Table
While preparing for a whole different topic, I had to create a few demonstrations of using custom SQL Types and Collections in both SQL and PL/SQL. While nothing spectacular – and for anyone who attended our 7Up class pretty familiar stuff – I thought an example of these small demos […]