|
|
|
|
This paper presents a compilation of quirky questions, tantalizing puzzles and cheeky challenges that will have you wander among many interesting, useful and often surprising features and functionality, sometimes as old as Oracle 7.2, yet often overlooked and forgotten. While some of the stuff will challenge and astound even seasoned developers, most can be understood and appreciated by developers with intermediate programming skills in SQL and PL/SQL. I assure you that anyone reading will have ‘since when is that possible’ experiences!
Many of the topics in this paper are not entirely my doing. I do not live on an island and there are so many excellent writers on the internet that write articles, tips, suggestions etc. that have heavily inspired me. Tom Kyte, his books and his AskTom website should really be mentioned here.
By the way, most examples are taken from the Oracle 7Up workshop that we have developed at AMIS, to help Oracle developers familiar with SQL and PL/SQL as it was in Oracle 7, quickly get up to speed with the many really useful features that have been added ever since. You will also find most examples described in more detail on the AMIS Technology Weblog, http://technology.amis.nl/blog.
In-Line Views
Around since Oracle 7.1, the In-Line Views are an incredibly powerful instrument
to achieve sometimes very simple things that otherwise can be quite complicated.
One of the questions I very often ask during job interviews is: how do you
retrieve the top 3 earners from table EMP. You would be surprised how often the
answer is erroneous, something like:
select ename , sal from emp where rownum < 4 order by sal desc /
The correct answer is very simple really once you are familiar with in-line views, the ability to replace the table or view in a select statement with an in-line query:
select ename
, sal
from ( select ename
, sal
from emp
order
by sal desc
) ordered_emps
where rownum < 4
I must confess I have largely ignored in-line views from 1995 until deep into the third Millenium. Now that I have (re)discovered them, I use them all the time. Recently I found out that you can even make use of in-line views within DML statements, such as MERGE and UPDATE. For example, to update the salary for all employees working out of Boston, you may initially thinl of this solution:
update emp
set sal = sal * 1.05
where deptno in ( select deptno
from dept
where loc = ‘BOSTON’
)
However, using an in-line view, we can perform the same operation as follows:
update ( select m.sal
from emp m join dept using (deptno)
where loc = 'BOSTON' )
set sal = sal * 1.05
Note: to prevent ORA-01779: cannot modify a column which maps to a non key-preserved table, proper constraints have to be defined on and between the tables in the in-line view.
How to become really unpopular
If you really want to have some fun – and have your co-developers start wondering whether they are losing it altogether – you could consider spending a few minutes getting your head round the dbms_advanced_rewrite package, first introduced in Oracle 10g. This package is primarily intended to inform the CBO (Cost Based Optimizer) that certain queries requested by applications and end-users can actually be executed in a different way and still yield the same result. This typically allows the use of summary tables (other than Materialized Views), OLAP results held in Analytical Workspaces (see for example
Query Equivalence by Mark Rittman) or any other alternative data source.
Here is how to trick your friends:
begin DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'my_first_equivalence', 'select ename from emp' ,'select dname from dept', false); end; /
When they now execute the most innocent statement available in any Oracle Database:
select ename from emp /
Here is their result:
ENAME -------------- ACCOUNTING RESEARCH SALES OPERATIONS 4 rows selected.
The database was instructed to treat select ename from emp as if it had been asked select dname from dept, so that is what it does! For more details: see http://technology.amis.nl/blog/index.php?p=447
To return to normal:
begin
DBMS_ADVANCED_REWRITE.drop_REWRITE_EQUIVALENCE('my_first_equivalence');
end;
Query results going Topsy-Turvy
Pivoting records means changing their format and typically either splitting or merging records. Usual examples include transactional records such as a list of daily transactions that need to be turned into a record structure, for example a weekly record. The inverse operation is also sometimes required. You can also use pivoting to get a nicer, more compact result from a SQL query without procedural logic.
Pivoting (both merging and splitting) can easily be implemented using Table Functions, at least as of 9i. Table Functions require you to create an object type (most of the times), a nested table type and a PL/SQL Function. For a simple (!) query, that may be a lot of trouble. And although Table Functions can be made quite efficient – they even allow parallel execution – they still can probably not compete with pure SQL based solutions. Another approach can be based on the SQL Model clause, introduced in 10g. We will see two other pivot approaches, using 8i functionality.
Suppose we have the requirement for a query that produces the following result:
DEPTNO HIGHEST_PA SECOND_HIG THIRD_HIGH
------- ---------- ---------- ----------
10 KING CLARK MILLER
20 SCOTT FORD JONES
30 BLAKE ALLEN TURNER
This shows all departments with for each department the three best paid employees. The first solution can be used even in an Oracle 7 database: no fancy features such as Analytical Functions are required:
select deptno
, max(decode(seq,1,ename,null)) highest_paid
, max(decode(seq,2,ename,null)) second_highest
, max(decode(seq,3,ename,null)) third_highest
from ( SELECT deptno
, ename
, ( select count(*)
from emp e2
where e2.deptno = e1.deptno
and e1.sal <= e2.sal) seq -- number of employees in the same department earning
-- the same or more; this is equivalent to ranking
FROM emp e1)
where seq < = 3
group
by deptno
order
by deptno
A more advanced way of doing this is the following, that does make use of Analytics (8i Enterprise Edition, 9i Standard Edition):
select deptno
, ename nr1
, nr2
, nr3
from ( select deptno
, ename
, lead(ename,1) over (partition by deptno
order by sal desc) nr2 –- add the next one in salary line
, lead(ename,2) over (partition by deptno
order by sal desc) nr3 -- also add the second next one in line
, rank() over (partition by deptno order by sal desc) rnk -- ranking in the department
from emp
)
where rnk = 1 -- only select the numbers 1
See the weblog article on pivoting at http://technology.amis.nl/blog/index.php?p=300 for more details.
Updateable External Tables
You probably know External Tables, new in Oracle 9i, that allow you to access data in files as if they were in tables. You write select statements using regular SQL to read, filter, join and sort the data in the files, just like normal tables. However, External Tables are not like normal tables when it comes to DML: you simply cannot Insert, Update or Delete into or from External Tables. That is… unless of course you make use of a simple workaround.
First of all, we wrap the External Table in a View:
create or replace view ext_tab_vw as select rownum rownumber , et.* from ext_tab et order by rownumber asc
Selecting data from this view will give you all records from the file underlying the external table. To get to DML: the essence is that we create an Instead-Of Trigger on this view. Any DML operation you attempt on the view will be intercepted by the trigger that will turn the statement around and, through UTL_FILE, update the file. Notice the use of ROWNUM in the view definition that allows for much quicker location of the relevant line in the file. By the way, we have used the dbms_lock package to have the instead-of code acquire a logical lock on the file before attempting to edit it, to prevent multiple statements from multiple sessions interfering with each other.
Now DML on what essentially is the External Table is supported and will lead to modification of the underlying file:
update ext_tab_vw set col2 = col2*3 where col1 = ‘X’
See: the article Updateable External Tables (http://technology.amis.nl/blog/index.php?p=491) on the AMIS Technology Weblog.
Whoops Management and Correcting History
Undoubtedly, you have heard some of the ‘flashback’ features of the Oracle 9i and 10g databases. The various options include Flashback Query, Flashback Table and Flashback Database. I won’t go in any details, but just give a simple example that incidentally will work in 9iR2 as well as 10g.
Suppose I just somewhat accidentally screwed up the data in one of my production system tables:
update emp set sal = (1+ dbms_random.value) * sal / commit;
Using Flashback Query, it is quite simple to correct this, lazily assuming that no one else recently touched the table:
update emp
set sal = ( select sal
from emp as of timestamp sysdate - 10/60/24 emp_before_oops
where emp_before_oops.empno = emp.empno
)
/
Here we update emp from an in-line view that select salary values as they were approximately 10 minutes ago. Note that the ability to look back in time depends on the undo data available to your system.
In 10g, we can be even more at ease: the database itself can provide the SQL required to undo a transaction and return the row in question to its prior state. In 10g I can use the following query – with the pseudocolumns versions_startscn, versions_endscn, versions_operation and versions_xid – to show the history of the data in my table, in so far still available from the UNDO data. Note that if I wait too long, I may not find the previous state of my records as it may have been moved out of the UNDO area.
SELECT versions_xid
, versions_startscn
, versions_endscn
, DECODE( versions_operation
, 'I', 'Insert'
, 'U', 'Update'
, 'D', 'Delete'
, 'Original'
) "Operation"
, ename
, sal
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
/
I have used the VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE clause to specify that I want to look at the history of EMP. With MINVALUE and MAXVALUE you can indicate that you want the maximum history still available from the UNDO_DATA. Instead of these keywords, you can also specify explicit values for SCN, such as 1937296253. Alternatively, you can use TIMESTAMP instead of SCN. I have summarized some of the results from this query:
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ENAME SAL
---------------- ----------------- --------------- -------- ---------- ----------
040024006B4B0000 1937296253 Update MILLER 1690
040024006B4B0000 1937296253 Update WARD 6500
...
1937296253 Original WARD 5000
1937296253 Original MILLER 1300
Using the versions_xid column we can go to the view flashback_transaction_query that provides UNDO_SQL per row:
SELECT operation , logon_user , commit_scn , undo_sql FROM flashback_transaction_query WHERE xid = '040024006B4B0000'
The results look like this:
Fri Apr 22
Current FLASHBACK_TRANSACTION_QUERY Contents For Selected Employee
User Table Commit
XID# Operation Logon Owner Table Name SCN
---------------- ---------- ------ ------ ------------ ---------
UNDO SQL
--------------------------------------------------------------------------------
040024006B4B0000 UPDATE SCOTT SCOTT EMP #########
update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAAUmPAAEAAABVXAAN';
...
The UNDO_SQL is created per record. The reason is simple: it is derived from comparing values before and after the transaction, not by interpreting the statement history. Since a record can be changed many times by different statements in a single transaction, this is only logical.
Unexpected Declarativity – Implementing Mandatory Master-Detail
It is a well-known fact that we cannot implement Mandatory Master-Detail business rules declarative in the Oracle database. As an example, let’s take ORDERS and ORDER_LINES: we can enforce that every ORDER_LINE must belong to an existing ORDER using a mandatory foreign key. However, the other half of the rule – the fact that ORDER must have at least one ORDER_LINE – cannot be enforced. That is, until I heard Tom Kyte recently suggesting the use of Materialized Views.
We use the following tables:
create table orders ( id number(4) primary key , order_date date ) / create table order_lines ( id number(10) primary key , odr_id number(4) not null , seq number(2) , quantity number(6,2) , product varchar2(200) ) / alter table order_lines add constraint ole_odr_fk foreign key (odr_id) references orders(id) /
We create a Materialized View, more or less based on the above query, that outer joins ORDERS with ORDER_LINES. This means that any ORDER is joined to all of its ORDER_LINES. If there are no ORDER_LINES for any given ORDER, it is outer-joined with an empty row. Note that MVs based on outer joins may not contain additional conditions in the WHERE-clause; therefore we need to use the first query rather than the even more compact second one.
create materialized view log on orders WITH SEQUENCE , PRIMARY KEY, ROWID (id, order_date) including new values ; create materialized view log on order_lines with SEQUENCE , rowid (id, odr_id) including new values; create materialized view odr_ole_mv refresh force on commit as select ole.odr_id ole_odr_id, odr.id odr_id from orders odr , order_lines ole where ole.odr_id(+) = odr.id /
At this point, we will make use of the fact that CONSTRAINTS can be defined against Materialized Views. Since we have stated that an ORDER must have children, any record in the Materialized View odr_ole_mv where ole_odr_id is null (the result of an outer join without having ORDER_LINES to join the ORDER with) is in violation. We can prohibit these records using a Check Constraint:
alter table odr_ole_mv add constraint "ORDER must have > 0 ORDER_LINE" check (ole_odr_id is not null) /
When we have enabled the Check Constraint, we can try to insert an ORDER. The insert goes through. Only, when we commit, we get the following error:
insert into orders (id, order_date) values (2 , sysdate -1) / commit / * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (TEMP.ORDER must have > 0 ORDER_LINE) violated
We have now enforced the other end of the Mandatory Master-Detail rule in a declarative manner. See for more examples: the weblog article Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints) (http://technology.amis.nl/blog/index.php?p=475). Note that Function Based Index also offer some interesting opportunities for declaratively implementing business rules, such as: there may only be one CLERK in each department.
Define your own aggregates
You are undoubtedly familiar with the aggregation operators in the database, such as max, min, avg, count and sum. These operators act on a group of records, calculating a single value over the entire group of records. The following statement probably looks pretty familiar:
select deptno , avg(hiredate) "Average Hiredate" from emp group by deptno /
You may surprised to learn that this statement will fail: you cannot calculate an average or a sum for DATE values; the error message is ORA-00932: inconsistent datatypes: expected NUMBER got DATE. Nor for VARCHAR2 values by the way. Well, in this case, there is an easy workaround for the average date. For example:
select deptno , sysdate - sum(sysdate- hiredate)/count(hiredate) "Average Hiredate" from emp group by deptno /
However, there is another approach, that is more fun, and also allows us to create pretty exotic aggregation functions. Let’s for example implement the SUM_VARCHAR2 operator, that allows the following query, returing comma separated lists of varchar2 values:
select deptno , sum_varchar2( ename) "Employees" from emp group by deptno
This can be accomplished through the so called Oracle Data Cartridge framework. This framework allows us to extend the database with new operators or domain indexes – in much the same way as was done for Oracle Text and Oracle Spatial. Creating a new aggregation operator is done in a two steps:
The object type we will implement has the following specification:
CREATE OR REPLACE type AmisVarchar2SumImpl as object
(
str varchar2(5000), -- The concatenated string, comma-delimited
static function ODCIAggregateInitialize(sctx IN OUT AmisVarchar2SumImpl)
return number,
member function ODCIAggregateIterate(self IN OUT AmisVarchar2SumImpl,
value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN AmisVarchar2SumImpl,
returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT AmisVarchar2SumImpl,
ctx2 IN AmisVarchar2SumImpl) return number
);
/
Note that the four functions starting with ODCI are mandatory; you must implement these functions in order to use the object type as foundation for a user defined aggregator. The initialize function is called by the SQL Execution Engine before processing a group of records is started. It is the place to do some setting up. In this case, where we will concatenate strings together, the setup consists of preparing an empty string. The iterate function is called for every record in the group. It has the actual value of the column or expression that is being aggregated as an input parameter. The terminate function is called by the database to retrieve the result of aggregating the group. The merge function finally is called when the aggregation has been performed as parallel operation and multiple results must be combined. We have not implemented this here. Note that the data type of the returnValue in the terminate function must be the same as the data type of the value input parameter in the iterate function.
The object type body has the actual implementations:
CREATE OR REPLACE type body AmisVarchar2SumImpl is static function ODCIAggregateInitialize(sctx IN OUT AmisVarchar2SumImpl) return number is begin sctx := AmisVarchar2SumImpl(null); return ODCIConst.Success; end; member function ODCIAggregateIterate(self IN OUT AmisVarchar2SumImpl, value IN varchar2) return number is begin case when self.str is not null then self.str := self.str||', '||value; else self.str := value; end case; return ODCIConst.Success; end; member function ODCIAggregateTerminate(self IN AmisVarchar2SumImpl , returnValue OUT varchar2, flags IN number) return number is begin returnValue := self.str; return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT AmisVarchar2SumImpl, ctx2 IN AmisVarchar2SumImpl) return number is begin return ODCIConst.Success; end; end; /
In order to inform the database that we have a new aggregation function, we have register a new PL/SQL function:,/P>
CREATE OR REPLACE FUNCTION SUM_VARCHAR2(input varchar2) RETURN varchar2 AGGREGATE USING AmisVarchar2SumImpl /
Now we can start to make use of our new operator, for example with the following query:
select deptno , sum_varchar2(ename) employees from emp group by deptno
The result is shown below:
Having the Database Count out Numbers
My requirement is that I can have a SQL query count out numbers, like ‘one, two, three’ or ‘two thousand and one, two thousand and two’ as well as ordinal numbers: ‘tenth, eleventh’ etc. And I want to be able to write queries that can return results like: ‘we hired one employee in 1980, we hired 8 employees in no year at all’.
We make use of two features: the SP and SPTH suffixes that we can in TO_CHAR expressions to turn numbers into spelled numbers and table functions that allow us to write a PL/SQL Function that procedurally creates records and subsequently use that function as if it were a table.
Table functions return a nested table type that contains standard (VARCHAR2, DATE, NUMBER) or user defined object types. For our example we will create our own object type and a nested table type of the number_type:
create type number_type is object ( number_value integer , spelled_number varchar2(200) , spelled_ordinal_number varchar2(200) ) / create type number_table_type is table of number_type /
Now we can create a table function that will return a number_table_type. However, we introduce a special feature of table functions: pipelining. Instead of building the entire collection of number_type objects and returning it when it is complete, we decide to pipe out individual objects as soon as they are created.
create or replace
function numbers_generator
( p_start in integer -- p_start should be greater than zero
, p_end in integer – p_end should be smaller than 5373484
) return number_table_type pipelined
is
begin
for i in p_start..p_end loop
pipe row ( number_type( i,to_char(to_date(i,'J'),'JSP'), to_char(to_date(i,'J'),'JSPTH') ));
end loop;
return;
end;
/
Now we can make use of this Table Function in a query like this:
select numbers.number_value value , numbers.spelled_number spelled , numbers.spelled_ordinal_number ordinal_spelled from table( numbers_generator(1,10)) numbers /
The result looks like this:
We can make use of the table function to return hiring data of our employees like this:
select 'We hired '||lower(numbers.spelled_number)
||' employee'||case when numbers.number_value >1 then 's' end
||' in '||nvl(to_char(emps.year), 'no year at all')||'.' hiring_record
from table( numbers_generator(1,10)) numbers
left outer join
( select extract(year from hiredate) year
, count(empno) count_hires
from emp
group
by extract(year from hiredate)
) emps
on (numbers.number_value = count_hires)
/
The inner query emps returns the years in which we have hired employees, along with the number of employees hired in that year. This set is outer joined on the number of hires with numbers, the result set produced by calling the table function numbers_generator for the numbers 1 to 10. The outer join in this case indicates that every number from 1 to 10 must be present in the result, whenever possible joined with a specific year that had that number of new hires. The result of this query looks like this:
Using the SUM_VARCHAR2 operator discussed earlier in this paper, we can go one step further and come up with the following query:
select 'We hired '||lower(numbers.spelled_number)
||' employee'||case when numbers.number_value >1 then 's' end
||' in '||nvl(year_count.years,'no year at all')||'.' hiring_record
from table( numbers_generator(1,10)) numbers
left outer join
( select count_hires
, sum_varchar2(cast(year as varchar2(10)) ) years
from ( select extract(year from hiredate) year
, count(empno) count_hires
from emp
group
by extract(year from hiredate)
) emps
group
by count_hires
) year_count
on (numbers.number_value = year_count.count_hires)
Pie Charts in SQL*Plus – for the really pathetic
One thing you probably never saw yourself doing is generating Pie Charts with simple SQL statements. I am not sure it is meaningful at all, but it can be done. Let me show you how.
At the heart of this story is the Table Function. Table Functions allow us to procedurally determine exactly what is returned in the query. In this case we can regard every record as a row in a matrix. A record is a single string with asterisks, dashes, dots etc. the positions (x-coordinates) where the graph should display something. The story is really quite simple: you pass a query that selects labels and values as a cursor to a table function -table( cursor (select label, value from table)) In SQL*Plus, we execute these commands:
set linesize 150
set pagesize 500
set long 200
select substr(column_value,1,150) "Piechart Salaries per Job"
from table( pie_graph
( cursor(select job, sum(sal) from emp group by job)
, 'Salaries per Job'
,30 -- diameter
, 0.15 –- threshold percentage to warrant a pie-slice
)
)
/
The function pie_graph that is invoked here looks as follows:
function pie_graph ( p_src in sys_refcursor -- ref-cursor for a query that returns labels in the first column and values in the second columns , p_title in varchar2 default null , p_radius in integer default 25 -- specifies the size of the piechart; , p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others ) return fixed_string_table
It relies on three Types being defined in the database:
create type string_table is table of varchar2(2000); create type values_tbl is table of number; create type fixed_string_table as table of char(150);
For the full story as well as all code, see the article Pie Charts in SQL - how pathetic can you get? at http://technology.amis.nl/blog/index.php?p=398.
Author
Thank you very much. You can reach me for any further background or discussion on the ideas presented in this paper at
jellema@amis.nl. You also find some additional code samples, resource links and screenshots on
http://www.amis.nl/technology.The AMIS Technology Weblog is found at
http://technology.amis.nl/blog, or through
http://www.orablogs.com/orablogs/.
About the author: Lucas Jellema worked some 8 years at Oracle Corporation, working within the Consultancy Department in the global iDevelopment Center of Excellence. He was active in diverse areas such as Oracle Designer, Oracle SCM and later JDeveloper, Java and some Business Intelligence. He “invented” CDM RuleFrame and was architect for Oracle Designer Web Assistant, now known as Repository Object Browser. His last stint at Oracle was the development of the JHeadstart Designer Generator for generating J2EE applications from Oracle Designer. In 2002 he joined AMIS, a Dutch consulting firm specializing in Oracle and Java technology. More recent entries in his vocabulary include Toplink, Oracle Warehouse Builder, ADF, Spring and BPEL. He is making his 10th appearance as ODTUG Speaker this year, performing his 25th presentation.