Posts tagged collection
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 12c has extended the SQL syntax and functionality for joining with collections in such a way that the call to the PL/SQL function that creates the collection can take an input parameter taken from the records from the table to which it is joined. That is a little abstract, so let’s quickly look at an example:
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)4
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 decoupled, reusable API that exposes services based on tables and views to clients that speak SQL, AQ, PL/SQL, Types, XML or RESTful, through SQL*Net, JDBC or HTTP.
This session shows through many demonstrations how types and collections are defined, how they are used between SQL and PL/SQL and how they can be converted to and from XML and JSON and how they drive Native WebServices as well as RESTful services based on the Embedded PL/SQL Gateway. Everyone doing PL/SQL programming will benefit immediately from this session. Every Database Developer should be aware of Types and Collections. For structured programming, for optimal SQL to PL/SQL integration and for interoperability to client application. This session introduces Types and Collections, their OO capabilities, the conversion to XML and JSON, their use in Native and RESTful WebServices and the pivotal role they can play in More >
Retrieving the values for domains in SQL queries in the Oracle RDBMS – months, days, numbers and custom value sets1
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 of some measure per month. Or the days of the week, to do something similar. Sometimes we just need a list of numbers in a certain range. And it is quite common to outer join such a collection of domain values with the set of measurements, using the partition outer join feature – to ensure that for each category of measurements, there is an aggregate for each of the values in the domain.
This article demonstrates several examples of standard domains, some SQL tricks for composing domains out of thin air and a number of features to spell out numbers and internationalize days and month names.
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 simple database means, we can establish some nice decoupling. Right here in SQL.
This article introduces a rather thorough case of decoupling. An example of executing a SQL Query that does not hit a table. The query addresses a View and returns data, without ever touching a table. The View supports DML and will reflect the results of the DML in subsequent queries. However, no table is used at all. The View is a complete decoupling mechanism that absorbs the query, turns it into a PL/SQL call and returns the results from PL/SQL. Through an Instead Of trigger, it does the same thing for DML. So when you are looking for decoupling – and you ought to be – look no further than to this simple, straightfoward View example.
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 quickly filter on records or list ‘similar’ records. We will use some valuable SQL and PL/SQL facilities: Collections, the Table operator and the Collect aggregator. (more…)
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 obtain. No single person or no single job will score one 100% on all your criteria. Unfortunately, such binary searches is exactly what SQL is good at. WHERE ALL CRITERIA ARE MET is the filter SQL applies to the records inspected. Of course you can use OR operators to leave some room for non-perfect matches and instead of using the equality (=) you can go for between or LIKE. But a structured approach to ‘fuzzy’ searches where the shades of gray most searches inevitably end up in is not readily available.
The prequel to this articles takes a few first steps in illustrating how scores against different criteria can be collected, how different weight factors for search criteria can be taken into account and how the first two search-match-patterns (number range, discrete values) started to emerge. One major question we ended that article with: how can we explain when the search is performed and the match scores for each record is More >