Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.
This article shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.
{ 0 comments }
An approach for comparing two tables for differences in data, using a pure SQL solution:
In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables.
The nature of differences fell into one of these buckets:
Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.
{ 0 comments }
Easily transpose columns as rows in Oracle 11G+, with the UNPIVOT clause in SQL.
A typical scenario:
{ 0 comments }
Oracle can easily compare data items of scalar data types (those that hold a single data value with no internal components — e.g. NUMBER, DATE or VARCHAR2). So, if a and b are two NUMBER variables, all you need to do to check whether they are the same or not, is test "if (a=b)".
Can we extend this simple equality check to instances of PL/SQL collections or object types (UDTs)? Let’s find out.
{ 3 comments }
When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.
The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.
ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.
{ 5 comments }
A typical query scenario: you want to sort data in descending order, say students arranged by their GMAT scores. Given a table student (id, name, score), what can be simpler than adding an ‘ORDER BY score DESC’ to the query?
If that’s what you thought, here’s a complication. There are some students who did not take the GMAT at all. Their scores in the table are not zero, they are NULL. Oracle’s ORDER BY..DESC in this situation could give you a nasty surprise with the result.
Here’s how the result will look with the ORDER BY…DESC clause:
{ 3 comments }
In Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable.
A big mistake.
See how NOT IN and NOT EXISTS behave differently in this small example.
{ 2 comments }
DECODE and CASE statements in Oracle both provide a conditional construct, of this form:
if A = n1 then A1
else if A = n2 then A2
else X
Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.
Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.
{ 71 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
