Weblog for the AMIS Technology corner
Posts tagged collection
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)
Dec 12th
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 encapsulation and decoupling.
The slides can be reviewed here:
View more presentations from Lucas Jellema.
Resources
Download Slides plus Demoscripts here: AbsolutelyTypical_UKOUG2011_jellema.zip.
Retrieving the values for domains in SQL queries in the Oracle RDBMS – months, days, numbers and custom value sets
Dec 24th
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.
Extreme Decoupling in SQL – Select and DML against a table that is not a table at all
Dec 22nd
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
Mar 6th
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. Read the rest of this entry »
Analyzing Match Scores in ‘fuzzy searches’ – explaining the shades of gray in a non-binary world
Sep 20th
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 known, how the score was arrived at. What criteria did the record meet – to some extent- and which ones did it fluke completely.
This article demonstrates a slightly different approach to the fuzzy search challenge and shows a way to not only calculate the scores but also construct a detailed analysis of the composition of the score.
Read the rest of this entry »
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)
Jul 16th
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 exchange of data. However, also one that is not extremely straightforward to get started with. In this article an example of how to send a TABLE OF MY_TYPE objects as an input parameter to a stored PL/SQL procedure. Read the rest of this entry »
Juggling met SQL Types in SQL and PL/SQL – fading borders between Collection and Table
Jul 14th
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 of how to create and use Type and Table of Type with for example the TABLE operator (to cast the Table of Type Collection into a relationally accessible construct) and the Merge operation as well as the Collect aggregator for producing quickly a Collection from the result of a simple SQL Query, might be useful. If for no one else, than for me – as I always tend to forget the exact syntax.
Note: all scripts are in a file you can download at the end of this article. Read the rest of this entry »
