tag:blogger.com,1999:blog-73959774118596198922012-01-31T23:24:52.095+01:00<a href="http://forums.oracle.com/forums/message.jspa?messageID=1732482#1732482">Simplicity is the ultimate form of elegance and sophistication</a>Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger1361100tag:blogger.com,1999:blog-7395977411859619892.post-49480856063941766702011-09-25T12:03:00.000+02:002011-09-26T00:02:59.681+02:00

Most of us know the SQL keyword EXISTS as the condition with a subquery you use in a WHERE clause. But if you look at the documentation of EXISTS, you'll see that it says nothing about just using it in a WHERE clause. It's just a sort of function that accepts a query as input and returns "TRUE if a subquery returns at least one row". The SQL language doesn't know about booleans, but it calls

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-35772064520540387762011-07-12T22:00:00.001+02:002011-07-12T22:20:05.828+02:00

I have experienced a strange bug when constructing an example showing how a partitioned outer join works. I used a setup which resembles a situation I have consulted about recently for timeseries data. The table can hold multiple timeseries whose data is coming from multiple sources. The data itself contains volumes that are reported for every 5 minutes. This table looks like this:SQL> create

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-21179935554437054812011-07-11T21:59:00.002+02:002011-07-11T22:10:22.902+02:00

Marcus Matzberger has translated five of my posts related to the SQL Model Clause for the German speaking Oracle community. Those posts now contain links to the German versions and I've listed them here for convenience as well.SQL Model Clause Tutorial Part OneSQL Model Clause Tutorial Part TwoSQL Model Clause Tutorial Part ThreeCalculating probabilities with N throws of a dieChoosing Between SQL

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-87330764905204715802011-06-11T23:22:00.001+02:002011-06-11T23:24:49.044+02:00

This morning I arrived back from a trip to Tallinn. Oracle Estonia had given me the opportunity to present my SQL Masterclass seminar at their training center in Tallinn, on Thursday and Friday. Thank you all those who spent two days hearing me. Here is a short story about my trip including some photos.I arrived at Tallinn airport around 1PM local time on Wednesday. My hotel room was located at

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-75265624432709011022011-05-26T18:00:00.001+02:002011-05-26T18:00:02.613+02:00

At CIBER we are doing a series of knowledge sessions where several colleagues are doing a one-hour presentation about one or two package of their choice. We have already heard several very interesting presentations about DBMS_HPROF, DBMS_PARALLEL_EXECUTE, UTL_DBWS, UTL_TCP and DBMS_FGA. And this evening a colleague will do a presentation about DBMS_PREPROCESSOR and DBMS_WARNING, and I will do one

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-89791175686777882662011-03-23T23:01:00.001+01:002011-03-23T23:03:10.689+01:00

Yesterday I attended OGh's APEX day. Previous year was already pretty successful with 150 people attending. This year the maximum of 150 people was reached in only a few days, so we scaled up the event to a maximum of 250 people, and in the end we still had to disappoint several people. And this year we had several sponsors as well: Oracle, Ciber, Transfer Solutions and Itium. These must be signs

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-7804229782199718382011-03-17T23:15:00.002+01:002011-03-19T00:14:32.661+01:00

Most PL/SQL developers will likely have witnessed this phenomenon several times during their career. But only in other people's code, of course :-). I'm talking about PL/SQL code where every program unit ends like this:exceptionwhen others then if c%isopen then close c; end if; raise;end;where lines 3 to 6 are repeated for every cursor in the block above.Proponents of open cursor paranoia

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-41039633099131136542011-03-15T22:13:00.002+01:002011-03-15T22:13:50.653+01:00

After a new version of software was installed in production, the end users reported a runtime error: ORA-01031: insufficient privileges, when selecting from a view. The developers of the code were investigating the problem and half way through, they asked me to have a look at the problem. I saw a function from schema3, which was used in a view in schema2, which was used by schema1. I had just

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-26069405210447669652011-02-27T15:55:00.005+01:002011-02-27T20:15:40.668+01:00

Last year I have presented a couple of times about the result cache. You can download this presentation from my new Presentations and papers tabpage, by the way. After each of those sessions I received several good questions. To some questions I didn't know the answer, and for some other questions I did, but I hadn't tested it so I wasn't absolutely sure. I promised to address those questions

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-50291346961009498962011-02-25T20:58:00.002+01:002011-02-25T21:24:57.812+01:00

On June 9 and 10, I'll be doing a SQL Masterclass seminar for Oracle University in Tallinn, Estonia. You can find the details here.

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-48911113352855112242011-02-15T22:08:00.006+01:002011-02-17T14:04:57.980+01:00

This post contains four unrelated notes.First a small SQL*Plus tip. I really like to know with which user I am connected to which database, so in my login.sql script I used to have this section:define gname=idlecolumn global_name new_value gnameselect lower(user) || '@' || substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_namefrom (select global_name,instr(global_name,'.'

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-16098543636529995582010-12-30T20:37:00.002+01:002010-12-30T22:24:10.847+01:00

Google has a lot of API's that you can use in SQL and PL/SQL as well. A couple of months ago I saw a very nice example on OTN here. It was an example of how to use Google's Translate API. Here is how to do it yourself.First of all, since version 11, you need to specify fine grained access to external network services, which is described here in the documentation. The package

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-39441813155454812802010-10-13T23:59:00.000+02:002010-10-14T00:09:07.669+02:00

In my presentation and paper about grouping last year, I mentioned that an aggregation query without a group by clause is the same as grouping by the empty grouping set. So this query:SQL> select count(*) 2 from emp 3 / COUNT(*)---------- 141 rij is geselecteerd.is the same as:SQL> select count(*) 2 from emp 3 group by () 4 / COUNT(*)---------- 141 rij is

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-90667053498637363422010-09-26T17:34:00.002+02:002010-09-26T23:22:51.127+02:00

Yesterday I came back from my third Oracle OpenWorld. This year I was not on a bloggers pass, so I feel less obliged to cover the event. But I know there are people out there that like to read about my experiences and I still like it very much at San Francisco, so here is a small write-up about my experiences at Oracle OpenWorld 2010.The event started at Sunday, but later than usual: 12:30 PM I

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-59638926612350823412010-09-17T21:53:00.002+02:002010-09-18T00:38:50.183+02:00

Tomorrow I'm off to Oracle OpenWorld for the third time in a row. I'm looking forward to a week attending great sessions, meeting friends, visiting the appreciation event, the Oracle ACE dinner and the Blogger's Meetup and presenting my session about the result cache. If you want to attend that session, the session details are:ID#: S319106Title: Oracle Database 11g’s Result CacheDate: 22-SEP-

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-75711762642820079782010-07-15T22:10:00.005+02:002010-07-15T22:32:17.555+02:00

I needed a function to determine how many hours fit in a given day. In the Netherlands, a function that does "return 24;" is not good enough. In the northern hemisphere, Daylight Saving Time is quite common. And in Europe, except Iceland, we have European Summer Time which states that the clock moves one hour forward on the last Sunday in March and it moves one hour backwards on the last Sunday

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-48685594069375592792010-07-15T19:21:00.001+02:002010-07-16T15:20:37.233+02:00

In Google Analytics I noticed a strange peak in my page visits. On a normal weekday when I haven't posted something new, approximately 200 people visit one or more blogposts here. But on Thursday July 8, there were 299. When zooming in on that day, I noticed that my blogpost about SAVE EXCEPTIONS was visited 101 times, where 5 or 10 per day is normal for that blogpost. When something like this

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-30381899698143669672010-06-20T11:40:00.004+02:002010-06-20T14:31:03.610+02:00

Previous year I already expressed some of my thoughts on the OTN Forums and Stack Overflow here. In short my story was: I absolutely love the Q&A engine of Stack Overflow, but the best answers can be found on OTN. However, with more knowledgeable people appearing on Stack Overflow, the gap is closing. In the comments section and from conversations I came to know that there is still some

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-12620838386291972012010-06-15T23:30:00.004+02:002010-07-10T23:28:57.791+02:00

Oracle Mix has a Suggest-a-Session facility for Oracle OpenWorld 2010. Some 120+ sessions are already suggested and you can vote on the sessions you'd like to see on Oracle OpenWorld. The sessions with the most votes will be selected in the program.My proposal is a session about Oracle 11g's Result Cache. Already 24 people have voted for my session (thank you!), but it looks like I need a couple

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-31076869938942968302010-06-15T23:09:00.002+02:002010-06-15T23:27:53.088+02:00

From version 11 onwards, the analytic functions LAG and LEAD have the extra option to RESPECT or IGNORE NULLS. This comes in handy when -for example- you don't want a regular next value like this:rwijk@ORA11GR2> select empno 2 , comm 3 , lead(comm) over (order by empno) next_comm 4 from emp 5 order by empno 6 / EMPNO COMM NEXT_COMM---------- ---------- ----------

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com4tag:blogger.com,1999:blog-7395977411859619892.post-12949047480886722712010-05-31T21:48:00.002+02:002010-06-01T00:10:46.072+02:00

There was a nice question on OTN today about whether there is a standard Oracle function to calculate the exponential moving average. The answer is that there is no such function, but with the model clause, you can calculate it very easy. And it's a great example of what I mean with "variable number of calculations based on calculated values", written in my third part of the model clause

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com4tag:blogger.com,1999:blog-7395977411859619892.post-44854577580534250432010-04-01T21:09:00.002+02:002010-04-01T21:17:46.484+02:00

Ok, I may be the last person seeing this, but I thought this is just too funny not to share it. I found a way to get rid of those annoying speeding tickets.

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-72152379704708521812010-03-30T23:44:00.001+02:002010-03-31T10:40:34.634+02:00

Today was OGh's APEX day. I saw 5 good presentations, in which I was not bored by Powerpoint once. I probably missed a few other good ones, since in the afternoon we all had to make some hard decisions about which presentation to visit and which ones to skip.The first presentation was by Kitty Spaas and Mark Rooijakkers called "Centraal Boekhuis goes APEX". An informative and very entertaining

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-74190531491463829442010-03-29T22:37:00.001+02:002010-03-29T22:30:42.875+02:00

To insert the data from an XML document into multiple relational tables, you can use PL/SQL, loop statements and the extract function. But I wanted to know whether it is possible to do it using only SQL. With the XMLTable function you can transform an XML document to a relational format. This post will show how you can use the XMLTable function to store the data into relational tables with a

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-68706181667828129652010-03-08T22:42:00.002+01:002010-03-08T23:07:33.136+01:00

PL/SQL got me fooled today. My assignment was to build a new procedure that gets invoked together with an existing procedure. After I had build and unit tested my new procedure, the tester wanted to conduct a system integration test. He had trouble coming up with a situation where the old and new procedure were called. So I helped him by having a look at some of the surrounding code and my

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-36838704723178961572010-02-15T22:04:00.003+01:002010-02-15T22:22:45.345+01:00

Tuesday March 30, the Dutch Oracle usergroup OGh organizes one entire day dedicated to APEX. I'm a little biased, as I helped organizing the event, but I think we succeeded in putting together a great program, as you can see here (warning: Dutch). The lineup includes some very well known APEX speakers like Patrick Wolf, Dimitri Gielis and Roel Hartman.The schedule also includes three sessions

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-33751558911026946592010-01-15T22:42:00.004+01:002010-01-16T11:11:02.594+01:00

To implement entity rules and inter-entity rules, one of the options is to use an on commit-time fast refreshable materialized view with a check constraint on top. You can read a few examples in these posts. It's an elegant way to check for conditions at commit-time, and quite remarkable when you see it for the first time. But there is a serious caveat to this way of implementing business rules,

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-18767053643064421902010-01-10T22:41:00.000+01:002010-01-10T22:41:34.365+01:00

At work I am building an interface using SQL object types and SQL collection types. I noticed a difference between using the COLLECT aggregate function in combination with a CAST function, versus the CAST-MULTISET method. I started out using CAST-COLLECT, but switched to CAST-MULTISET. Here is a simulation:rwijk@ORA11GR1> create table customers 2 ( id number(6) not null primary key 3

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-72383362510064959022010-01-03T16:08:00.005+01:002010-01-03T20:22:49.373+01:00

This was the title of a thread on OTN's SQL and PL/SQL Forum. Centinul posted an interesting puzzle to be solved by either SQL or PL/SQL here. If you like to puzzle yourself, then stop reading here, and go visit the link (and don't look at the answers of course :-) ).The puzzle was not only a great way to spend time, it's also a nice show case for hierarchical queries and the answer of Frank

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-32954187411666150792009-12-31T14:57:00.000+01:002009-12-31T14:58:14.026+01:00

In 2009 I have presented "Alles Over Groeperen" twice in the Netherlands and the English version "All About Grouping" also twice, once at the Oracle OpenWorld Unconference and once at UKOUG. If you haven't been able to visit one of these presentations, and you are interested in the subject, then you can catch up by reading the paper that I just finished.The usual sequence of events is to first

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-59272570388519783992009-12-16T16:00:00.001+01:002009-12-16T17:05:39.548+01:00

In version 11.1.0.6, Oracle introduced flashback data archives under the marketing term Total Recall. Previously you could only flashback your table to an earlier point in time, as long as the needed undo information was available. With flashback data archives you can extend this period with as long as you specify. You create a flashback data archive, specify a retention period and associate the

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-89154344804941734392009-12-09T00:39:00.000+01:002009-12-09T00:40:14.166+01:00

A colleague of mine sent me this link, and wanted me to have a look at point 5:Choose sys_guid() instead of sequences for populating meaningless PK-columns.Traditional sequences are used for populating an ID-column.Sequences have several disadvantages:• Sequences have to be created, maintained and deployed.• Sequences need PL/SQL-code to be fetched (the ‘select into’ construction is obsolete in

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com11tag:blogger.com,1999:blog-7395977411859619892.post-35221196931725194512009-12-03T17:03:00.001+01:002009-12-03T17:04:15.280+01:00

UKOUG Conference Series, Technology & E-Business Suite 2009 is over. Three days were filled with highly technical presentations in several streams. It was my first time, and I hope it wasn't my last time.The presentations I saw were:John Scott - Building Scalable Applications with Oracle Application ExpressJoel Goodman - Oracle Database Links part 2 - Distributed TransactionsJonathan Lewis -

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-85522594828062074652009-11-24T23:38:00.000+01:002009-11-25T08:49:33.449+01:00

Version 11g release 2 introduced recursive subquery factoring or the recursive with clause. This is an extension to the SQL syntax with which you can do recursive/hierarchical queries. However, since version 2, Oracle has had the connect-by clause for hierarchical queries. And at first glance, the connect-by and the recursive-with seem very similar in what they can do. But on a second look, there

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-86756498783524786582009-11-17T22:04:00.001+01:002009-11-18T09:15:36.064+01:00

This year, the Dutch Oracle usergroup OGh has started to offer a set of 4 presentations, called the ACE Cases. Each presentation is given by a Dutch Oracle ACE about his specialty. The first one was about XMLDB by Marco Gralike. The second one was about SOA Governance by Mike van Alst. For the third one, OGh had invited me to talk about something related to the subject "database". Today I had the

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-17150742783814162792009-10-28T23:45:00.000+01:002009-10-28T23:47:46.551+01:00

In version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. These are not normal hints like we know them. Oracle writes:Note:The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com16tag:blogger.com,1999:blog-7395977411859619892.post-38035996124317400742009-10-18T11:57:00.000+02:002009-10-18T11:59:25.644+02:00

On Thursday 10AM I gave a presentation at the Unconference called "All About Grouping". I really need to think more about titles of presentations, as two people came in with the question "What kind of grouping will you be talking about?". Fortunately, when I mentioned it was about grouping in SQL and especially the extensions ROLLUP, CUBE and GROUPING SETS, they stayed.I had given the

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-53741868259075663882009-10-16T18:04:00.001+02:002009-10-16T18:07:19.326+02:00

Tuesday was a very rainy day. I decided to take the shuttle bus to the Moscone and was therefore a little late for the first session. The sessions I visited this day were:Dan Morgan - What's New in Eleven ... Dot Two (that Oracle won't be talking about)In every release there are like two types of features. On the one side there are features that are picked up by marketing and which are given

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-51752218218643487362009-10-13T16:56:00.000+02:002009-10-13T16:58:02.646+02:00

This year I'm visiting Oracle OpenWorld for the second time. Everything is now familiar, except for two differences. First one is the location of Oracle Develop, which has moved back to the Hilton Hotel, instead of the nearby Marriot Hotel. It is a nice walk, but sometimes you cannot chat after a session if you have a next session at the Moscone Center. Second difference is that it seems to me

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-48475294367122740462009-09-14T22:47:00.000+02:002009-09-14T22:48:35.391+02:00

Remember the opening statement of the MV error series? It was "Never put more than one error condition into one error message". Two months later I realized I didn't practice what I preach myself.I'm currently busy developing a Tour de France application at home. The application has to store the standings. After each stage, a daily and overall standing becomes available for each of the following

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-32137085883823532732009-09-14T20:53:00.001+02:002009-09-14T20:55:28.519+02:00

My plan for 2009 was to try to do a presentation about grouping in SQL at both Oracle OpenWorld and at UKOUG. I submitted abstracts for both conferences. Unfortunately, the session was not selected for Oracle OpenWorld. Not very surprising really, since it is about functionality that was introduced in versions 8 and 9, so I can imagine other subjects are more important for Oracle. A bit later I

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-58013121032324274872009-09-13T16:55:00.000+02:002009-09-13T16:56:39.520+02:00

Previous posts:Fast refreshable materialized view errors, part one (basis MV's)Fast refreshable materialized view errors, part two: join MV'sFast refreshable materialized view errors, part three: aggregate MV'sFast refreshable materialized view errors, part four: union all MV'sFast refreshable materialized view errors, part five: nested MV'sFast refreshable materialized view errors, part six:

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-39183855091935187182009-09-13T14:27:00.000+02:002009-09-13T14:28:17.962+02:00

After you've been confronted with an error message during the creation of your fast refreshable materialized view, you either know exactly what to do next to resolve the error, or not. If not, the MV_CAPABILITIES_TABLE might help. You can read about it in the documentation here. I remember trying to use it in my project half a year ago and I didn't find it useful. After reading this post, you'll

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-30630049307740306412009-08-16T00:54:00.002+02:002009-08-17T10:03:48.127+02:00

Previous post about union all MV'sThe restrictions regarding nested MV's are not in the fast-refreshable part of the documentation, but a little earlier: here.I noticed three restrictions in there:1) All parent and base materialized views must contain joins or aggregates.2) All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-74499249133967125652009-07-31T23:38:00.000+02:002009-07-31T23:39:28.292+02:00

Previous post about aggregate MV'sThe fourth part will discuss the restrictions and the accompanying error messages of union all MV's. There are only a few restrictions mentioned in the documentation here.Again, I numbered them and edited them a little bit to contain only one restriction per item:1) The defining query must have the UNION ALL operator at the top level.2) Each query block in the

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3tag:blogger.com,1999:blog-7395977411859619892.post-65476356829991141292009-06-26T01:08:00.001+02:002009-06-26T01:10:54.483+02:00

Previous post about join MV'sIn the third part I'm going to examine all restrictions for aggregate materialized views, as described in the documentation.For convenience of this post, I have numbered and slightly rearranged them to contain one restriction per number:1) All restrictions from "General Restrictions on Fast Refresh".2) All tables in the materialized view must have materialized view

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-69203133136846716382009-06-05T21:47:00.002+02:002009-09-13T21:28:35.435+02:00

A quick intermezzo between the MV error related posts:For almost three years now, I am answering questions on a regular basis at the OTN Discussion Forums, mainly at the SQL and PL/SQL Forum. By reading answers and giving answers, you are not only helping others, but you'll also find you are learning at greater speed than before. Most of the regulars like the forums very much of course, despite

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3tag:blogger.com,1999:blog-7395977411859619892.post-88057140231509333882009-05-31T16:50:00.004+02:002009-06-01T23:10:57.216+02:00

Previous post about simple MV'sIn this second part I'll start with a fast refreshable join materialized view, based on the emp and dept table:rwijk@ORA11GR1> create table myemp as select * from emp 2 /Table created.rwijk@ORA11GR1> create table mydept as select * from dept 2 /Table created.rwijk@ORA11GR1> alter table myemp add primary key (empno) 2 /Table altered.rwijk@ORA11GR1> alter table

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-2653061924149995552009-05-30T20:50:00.001+02:002009-05-31T16:58:39.855+02:00

Never put more than one error condition into one error message. It's just so common sense, I didn't even put it in the latest version of Standards and Guidelines for application developers anymore. Maybe wrongly so, because I see violations of this rule a little too often.An example of what I mean: we have a company mail server that requires us to change our passwords every month. And when

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com11tag:blogger.com,1999:blog-7395977411859619892.post-8400301943346583582009-05-27T20:49:00.002+02:002009-05-27T22:20:14.843+02:00

Yesterday I attended the third version of the Planboard DBA Symposium. Ten presentations were given in two parallel tracks. Some presentations were a little too "hardcore DBA" for me as a simple developer, but still there were several ones I really liked. My highlights were Toon's presentation "APEX for DBA's" and Harald's presentation about SQL Plan Management. Both succeeded in making their

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-55413749308556146982009-04-09T23:27:00.002+02:002009-04-09T23:45:00.153+02:00

Well, I'm not the judge and I don't know what other solutions will be sent in, but I guess it's hard to beat this magnificent O(NlogN) solution to the First International NoCOUG SQL Challenge by Alberto Dell'Era, posted here on his website. If you have a statistics or mathematics background, you might actually be able to understand it completely. If not or if you forgot most of it, like me, then

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3tag:blogger.com,1999:blog-7395977411859619892.post-55794809828759526142009-03-19T22:35:00.003+01:002011-07-11T21:59:52.202+02:00

If you prefer to read in German, then you can find a translation here.On my post Calculating probabilities with N throws of a die, I received a comment by Narendra saying:I hope you are not serious about your last statement with some comments I'm sure it's not that hard to maintain....:)But I was serious. However, it's a sentiment I hear a lot: after my presentation Do more with SQL I received

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com15tag:blogger.com,1999:blog-7395977411859619892.post-53195007149667382022009-03-16T00:25:00.003+01:002009-03-16T00:30:40.172+01:00

Toon Koppelaars started blogging "About how database web application development nowadays has gone absolutely and utterly absurd". I cannot agree more. If you want to read why, then you should definitely add this blog to your reader.

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-22946586981666181572009-03-09T21:15:00.003+01:002009-03-09T21:40:19.181+01:00

At May 26 I will be doing a presentation at Planboard's Oracle DBA Symposium. The presentation is about grouping in SQL and more specific about rollups, cubes and grouping sets and how they work on the outside and on the inside.Even though the symposium is aimed at DBA's, I see several interesting presentations for me as a database developer as well. I'm looking forward to those presentations and

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-59495138123903112142009-03-09T20:09:00.003+01:002011-07-11T21:57:42.468+02:00

A translation of this post in German can be found here.Chen Shapira pointed me to a SQL riddle by Iggy Fernandez. Laurent Schneider already gave an impressively simple solution using a hierarchical query and the XMLQuery function. I used the SQL model clause (of course) to achieve the same:rwijk@ORA11GR1> select * from die order by face_id 2 / FACE_ID FACE_VALUE PROBABILITY---------- --------

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-26405084953425192982009-03-01T23:58:00.004+01:002009-03-05T00:12:04.096+01:00

To have an XMLType variable in your PL/SQL program, and filled with database content, I almost exclusively use the XML publishing functions like XMLAgg, XMLElement and XMLForest. But there are more options. You can use the dbms_xmlgen package for this purpose. And via a colleague who attended an AMIS session by Matthieu de Graaf early this year, I became aware of a third option. The XMLType

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-50109387941171363622009-02-10T23:41:00.003+01:002009-02-11T00:08:07.950+01:00

If you have setup a JMS queue with Oracle's Advanced Queuing, a question that pops up regularly is: "What's currently in the queue?". Now that can't be hard - and it isn't - yet I spent a silly amount of time getting it done. Hopefully this blog post will prevent others spending too much time.To setup the JMS queue, create a queue table with payload type sys.aq$_jms_text_message, and a queue in

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com7tag:blogger.com,1999:blog-7395977411859619892.post-6921627249298346622009-02-07T11:53:00.005+01:002009-02-13T15:46:28.346+01:00

The behaviour of the "for update skip locked" clause is not what I expected it to be. I expected it to try to lock all rows when opening a cursor, just like a regular "for update", and then just skipping the locked rows. Below is a test to show how it works in reality. The setup:A table with three rows:rwijk@ORA11GR1> create table t (id) 2 as 3 select level 4 from dual 5 connect by

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com14tag:blogger.com,1999:blog-7395977411859619892.post-26686224192564231682009-01-30T21:05:00.005+01:002009-01-31T13:51:22.909+01:00

This morning I saw an e-mail with the title "SQL puzzle?" in my inbox. That's a good title to make me read that one first. The poster was stunned by the results of the following two queries:SQL> SELECT COUNT(DISTINCT bdl.nrl_id) 2 FROM a_table bdl 3 WHERE bdl.periode >= to_date('20081023092701','yyyymmddhh24miss') 4 ;COUNT(DISTINCTBDL.NRL_ID)-------------------------

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-6704174680807728262009-01-19T21:00:00.001+01:002009-01-19T21:00:00.739+01:00

Today I gave a session about PL/SQL to colleagues who mostly work with Tibco in our Enterprise Application Integration (EAI) group. They know how to program and they know SQL. The challenge was to cover most of the main features of PL/SQL in a workshop in only 8 hours. The session contained lots of little exercises. Topics were architecture, datatypes, main statements, exception handling,

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-38874797280384361252009-01-06T01:17:00.002+01:002011-07-11T21:54:24.905+02:00

If you prefer to read in Dutch, then you can read the original article here (page 30-34), and if you prefer to read in German, then you can find a translated version here. Practicalities of the SQL Model ClauseIf developing SQL queries is part of your job, then it is definitely worth knowing the SQL model clause. Once you’ve gotten over the initial fear of the new syntax and learned the basics,

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-52943687585331766952008-12-29T17:32:00.002+01:002008-12-29T17:42:56.786+01:00

For all Dutch readers interested in the SQL model clause (I wonder how selective those two predicates are ...): the last part of the SQL Model Clause Tutorial is published in the winter edition 2008 of OGh Visie. The subject is "Practicalities of the SQL Model Clause" and it tries to answer the question for which kind of problems a model clause query is best applied.For all non Dutch readers

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3tag:blogger.com,1999:blog-7395977411859619892.post-69548949446543026432008-12-17T12:27:00.000+01:002008-12-18T00:28:14.126+01:00

My DBA colleague Ronald Rood asked an interesting question on OTN's SQL and PL/SQL forum. I'll repeat the question here. First the setup of the table:rwijk@ORA11GR1> create table sschedule ( 2 item varchar2(10) 3 , days varchar2(30) 4 , fixed_start_time varchar2(5) 5 , minutes number 6 ) 7 /Tabel is aangemaakt.rwijk@ORA11GR1> insert into sschedule (item,days, fixed_start_time, minutes

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-62647353989276472272008-12-05T15:44:00.001+01:002008-12-05T15:46:41.440+01:00

In this AMIS-post, Lucas Jellema was looking for a way to duplicate certain grouping sets for his ADF tree structure. Using ROLLUP this is not completely possible, but with GROUPING SETS it is, as I will show below. It was a small challenge however to distinguish the duplicate sets from each other in the select list and this is where I learned something new.For example, let's start with this

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-89333412585504991072008-11-23T00:23:00.001+01:002008-11-23T00:24:43.879+01:00

In our in-house application we are developing new features and fixing bugs with approximately 40 developers in total. Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package. When there's no time pressure, the

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-91989601366948477432008-11-16T17:33:00.001+01:002008-11-17T09:36:05.779+01:00

While doing a quality check on new code, I encountered some row by row processing. This always triggers me to make a remark about rewriting the code to a single SQL, or at least bulk processing. But in this case, inside the loop, several local variables were used to do some counting. And at the end of the loop, a processing report was created to inform the end user about how many records had been

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-88800474802580037012008-11-15T11:50:00.001+01:002008-11-15T13:14:06.843+01:00

Last year, with version 11.1.0.6, I investigated how useful the new flashback data archive was for implementing journaling on a table in this post. There were two main issues:1) The documented restriction "DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table.

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-21194937794566923352008-10-29T22:13:00.000+01:002008-10-29T22:13:19.656+01:00

According to Wikipedia, the year zero does not exist:B.C. (or BC) — Before Christ. Used for years prior to AD 1, counting backwards so the year n BC is the year 1-n AD. Using these two calendar eras as historians use them means that there is no year 0 or negative year numbers.But according to Oracle it does:rwijk@ORA11GR1> select date '0000-01-01' from dual 2 /DATE'0000-01-01'------------------

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-44072562527075538782008-10-18T18:31:00.004+02:002008-10-19T20:33:34.316+02:00

In two earlier posts, about 11.1.0.6 oddities and my last one about 11.1.0.7 I mentioned not being able to find the column dependency information used in fine grained dependency tracking in the regular USER_% views of the data dictionary. I still haven't, but on the second post, Toon Koppelaars commented:I did some research in this area a few months ago. The FGDT information is stored in

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3tag:blogger.com,1999:blog-7395977411859619892.post-77310154793429742342008-10-15T22:18:00.004+02:002008-10-15T23:25:14.615+02:00

Up until now I had only installed base versions of Oracle, but this weekend I decided to install the 11.1.0.7 patch. It was a rough ride, with some really weird error messages ("DBUA thinks this is a Rerun operation" and asking to remove a file which wasn't there). Developers shouldn't do these kind of things I suppose, especially developers who don't fully read the documentation... After doing

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-77595607822365087602008-10-14T10:09:00.001+02:002008-10-14T10:09:34.888+02:00

Still in the afterglow of my Oracle OpenWorld experience, I received yet another nice Oracle surprise: an e-mail telling me I'm now an Oracle ACE.You have been chosen based on your significant contribution and activity in the Oracle technical community. Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a willingness to share your knowledge and

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com11tag:blogger.com,1999:blog-7395977411859619892.post-56634167235757289012008-09-28T01:53:00.000+02:002008-09-28T01:54:20.673+02:00

Thursday morning I decided to switch sessions, Mogens Norgaard's for Tanel Poder's session. I had to queue up, because the room was fully booked, but not everybody showed up, so all people in the line-up could enter the room. I sure was glad I switched, as this was the best session at this conference for me. The title was "Advanced Oracle Troubleshooting: No magic is needed - a systematic

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-57068743905857516032008-09-26T04:27:00.003+02:002008-09-26T16:01:07.399+02:00

Before going to some sessions I had a walk around the two exhibition halls for the first time. Not very interesting for me: a lot of vendors exhibiting and some game rooms. The demo grounds were more interesting although it was a little bit too busy. I managed to see a preview of SQL Developer's data modeling extension, but more on that later. I was a little disappointed for not seeing a stand

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-89973780366102174832008-09-24T06:17:00.002+02:002008-09-24T08:09:38.036+02:00

Tuesday was very diverse. I missed the 9AM session called "How to hack an Oracle Application Express application", because I wanted to finish my previous blogpost. However, before the third session started, I heard two people behind me talking how great this session was ...So the first session I attended was Tom Kyte's keynote "The best way ...". I'm more a fan of technical sessions than keynotes

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-36879403748852904582008-09-23T15:37:00.003+02:002008-09-24T13:49:42.895+02:00

Monday started kind of funny. On Sunday, Chen pointed out an unconference session to me about the SQL and PL/SQL forum, saying semi-seriously "this one is about you". My first regular session started at 11:30 AM, and this one at 11 AM, so I decided to have a look what Greg Pike had to say about the forum. His point was that he was amazed at the speed and the quality of the answers in the SQL and

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-14198814299256783932008-09-22T13:30:00.005+02:002008-09-28T22:15:56.009+02:00

The first day at Oracle OpenWorld 2008 was great. I arrived early at 8.30 AM, although my first session started at 10.00 AM, so I had the time to register, explore the territory and take some photographs. The cable to connect by camera to my laptop is left at home unfortunately. I will put some photographs here when I get back home. My first impressions are that it is a huge and very well

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-46446489470856353012008-09-13T15:46:00.000+02:002008-09-13T15:47:32.623+02:00

Previous post in this series: implementing dynamic data constraintsThe CDM classification scheme has divided the change event rules into two categories: change event rules with DML and without DML. Since I have the same advice for both of these categories I am elaborating on them both in the same post.Simple default value rulesThese are implemented declaratively by specifying a default value

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-81704545550395038882008-09-13T11:08:00.006+02:002008-09-16T14:05:26.189+02:00

Inspired by this OTN post from michaels, I realized I can also do the unpivot query from Simulating print_table with unpivot with just grouping sets functionality. So you won't have to wait until 11g, because grouping sets became available somewhere in the 9i range. The equivalent query is this:rwijk@ORA11GR1> select case 2 when grouping(ename) = 0 then 'ENAME : ' || ename 3

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-85109955916313204502008-09-11T21:49:00.002+02:002008-09-11T22:54:31.848+02:00

This evening (and late afternoon) AMIS gave 9 speakers a chance to prepare their Oracle OpenWorld sessions for their Dutch peers. Three parallel tracks with three sessions each. You can read the official introduction for these sessions here. I decided to visit the three database oriented sessions, but some other sessions looked interesting as well. I'll check the feedback on those other sessions

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-2495925224598428902008-09-07T20:18:00.002+02:002008-09-07T23:49:20.855+02:00

A quick post because this blog is exactly one year old today. In this year, the blog has had 19,227 visits and 24,782 page views. A picture of Google Analytics:Some other facts:The maximum number of visits on one day is 212, on March 6th, due to this blogpost from Jonathan Lewis.Most popular post is Check constraints with sysdate, followed by SQL Model Clause Tutorial, part two and

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-769801718299134292008-09-07T00:12:00.005+02:002008-09-07T20:17:57.403+02:00

Last Friday I finally got approved to visit Oracle OpenWorld as a blogger. It came as a surprise, because I had to wait 5 weeks for the approval. This can be fine if they had said in advance that an approval process can take up to 5 weeks. But without such a notice, it's hard to imagine why judging that a blog is indeed about Oracle and is not brand sparking new, can take that long. During those

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com4tag:blogger.com,1999:blog-7395977411859619892.post-91150750427860719282008-09-04T21:18:00.003+02:002008-09-04T22:10:23.288+02:00

Just finished a three day APEX training by John Scott and Dimitri Gielis of Apex Evangelists in my home city. It consisted of 10 one-and-a-half-hour presentations, one hands-on session and a quiz. The topics of the 10 presentations were well chosen. No time was wasted by explaining basic stuff. Instead they focused on some lesser known topics you won't necessarily encounter, like AJAX and

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-26833598739180545812008-08-25T23:27:00.001+02:002008-09-13T15:50:04.215+02:00

Previous post: Implementing inter-entity rulesCreate rulesThese rules either originate from a decomposed inter-entity rule or they were invented on their own. Again you have two choices of implementing them: using database triggers or incorporating in your api's. And again it's a matter of whether you are willing to lose performance or not. You'll probably want to be consistent throughout all

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-32118510225853371552008-08-21T22:35:00.003+02:002008-08-25T23:28:53.058+02:00

Previous post in this serie: Implementing entity rules.Relationship rulesRelationship rules are implemented by foreign keys. This seems very straightforward, but there is a little more to it. You might have to add a not null constraint to the foreign key column to implement the optionality of the relationship. Or an extra unique key constraint on the foreign key column to implement a 1:1

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-79191359355374175642008-08-18T20:38:00.002+02:002008-08-18T21:02:14.554+02:00

The September/October 2008 issue of Oracle magazine is out. In this issue you'll find an article by Tom Kyte called The Trouble with Triggers. It ties in nicely with the current series in this blog about implementing business rules. And if I wasn't able to convince you that database triggers are evil, I'm sure that Tom will.

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-74270596847319534602008-08-11T22:54:00.003+02:002008-09-01T22:19:29.182+02:00

Previous post in this serie: Implementing attribute and tuple rulesAttribute rules and tuple rules were fairly easy. The fun part starts with the entity rules and - in a next post - the inter-entity rules. According to the picture above, entity rules come in two flavours: unique identifier rules and other entity rules. However, since the other entity rules can be implemented in two clearly

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-53523483187918007852008-08-07T21:16:00.003+02:002008-08-07T21:45:30.406+02:00

Today, Antoine Dinimant (Antoun) has released a translation of my SQL Model Clause Tutorials, part one and part two, on the French developers' community site developpez.com.The official title of the translated version is: "Intégrez un modèle de calcul à vos requêtes SQL : la clause MODEL d'Oracle" and can be seen here. The translated version looks very good and contains some extra notes by Antoun

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-27561804547295859192008-08-01T23:02:00.004+02:002008-08-11T22:58:19.179+02:00

Previous post: Implementing business rulesThe first of the five main classes of business rules according to CDM RuleFrame's classification scheme is the class "Static Data Constraint Rules". Each rule describes a state the data must always adhere to. By having them in place - and having them properly implemented of course - your data integrity is ensured. Static data constraints have 4 subclasses

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-85491053959185262392008-07-30T21:54:00.018+02:002011-02-17T11:09:02.331+01:00

This is the first post about implementing business rules in an Oracle database. In the previous millenium I've implemented them a lot using CDM RuleFrame and its business rule classification scheme. This was all done using Oracle7. And business rules were either implemented using check constraints or, most of the time, using database triggers. Since then, a lot has changed, which have changed my

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-46535925271186281722008-07-24T22:35:00.002+02:002008-07-24T23:16:48.313+02:00

If you use SYSDATE or SYSTIMESTAMP multiple times in a query that takes some time, will you see different times?No, because queries are read consistent: they see the data as of the timestamp the query began. So SYSDATE and SYSTIMESTAMP will produce the date and timestamp at the time the query begins, right? You can see this in this little test:rwijk@ORA11G> select sysdate 2 , systimestamp

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1tag:blogger.com,1999:blog-7395977411859619892.post-27941238381540167782008-07-19T12:04:00.004+02:002008-07-21T22:47:01.205+02:00

For some time now, I have recommended to use dbms_lock.request when implementing business rules correctly. In short, the idea behind it is to serialize access when validating some entity rule when the rows belong to the same parent record. For example, think of a business rule that checks for an overlap of periods. An alternative for dbms_lock.request is to physically lock the parent row. But

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-88247556351578280462008-07-11T22:47:00.003+02:002008-07-11T23:49:22.667+02:00

Just noticed that Christian Antognini started blogging a couple of weeks ago. I added the blog to my reader, hoping for more interesting posts like this one. Christian's book Troubleshooting Oracle Performance is available now. I'm impressed with the table of contents alone and so the book is on my wish list now. The book also has two forewords written by Cary Millsap and Jonathan Lewis that can

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-10338188191012294212008-06-09T23:32:00.005+02:002008-08-25T01:21:08.097+02:00

Today I will give an in-house knowledge session called "Do more with SQL". I want to share my knowledge about this topic, because many times I see procedural code being written where one or a few single SQL statements would have been sufficient. And not only sufficient, but much shorter, more readable and faster as well. I hope to raise awareness among my colleagues for this problem. And if this

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com3tag:blogger.com,1999:blog-7395977411859619892.post-82831313674033607542008-05-30T20:36:00.005+02:002008-06-02T10:21:30.345+02:00

There are lots of ways to do string aggregation. My favourite one is by using the model clause. Although you have to be aware of the increased PGA memory consumption. This can be a problem when you plan to use the query in multi user applications. A rare case if you ask me. It can also be a problem when a single session doesn't have enough PGA memory available. In the latter case it will read and

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com5tag:blogger.com,1999:blog-7395977411859619892.post-77289679553854882222008-05-19T20:48:00.003+02:002008-05-19T21:19:59.370+02:00

In a earlier blogpost of mine, I talked about Toon Koppelaars' new product called RuleGen. I promised to do some testing about the performance:However the trigger code itself seems to be relatively lightweight - I'll have to test this. And more important, multirow DML is capable of checking the rule only once instead of "for each row". Undoubtedly RuleGen will outperform CDM RuleFrame. In the

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-15844415750613455722008-04-28T15:03:00.002+02:002008-04-28T17:23:09.521+02:00

In my previous Fancy Code blog entry, I received a comment from Rick saying:Maybe the original author specified DETERMINSTIC so that the function wouldn't have to logically derive the return value if/whenever it was called again with the same parameter value. Since it could just return previously calculated results, then maybe he could save a fraction of a second of CPU time within a year or two

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com2tag:blogger.com,1999:blog-7395977411859619892.post-75228899059247996932008-04-17T21:54:00.002+02:002008-04-17T22:25:07.799+02:00

My customer recently upgraded some of its databases from Oracle 9.2.0.7 to 10.2.0.3, so when my colleague and me saw error message ORA-12899 after an insert statement, it came as a surprise. But it was a pleasant surprise.In 9.2.0.7 we were used to see a ORA-01401 in the situation where you'd insert a varchar2 value that doesn't fit into a column because it's length is too big. And to its

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0tag:blogger.com,1999:blog-7395977411859619892.post-63229357602232223902008-04-14T20:57:00.003+02:002008-04-14T22:06:04.323+02:00

I was reminded about this blog's motto when I watched a piece of production code last week. It was located as a subfunction at the end of the declaration section of a monster procedure containing 1500 lines. At least some form of modularization has kicked in, was my first thought after seeing the smallish function. This thought was immediately followed by a "and quite a clear name too". Then I

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com6tag:blogger.com,1999:blog-7395977411859619892.post-3349848289158705192008-04-13T10:17:00.002+02:002008-04-13T22:56:15.976+02:00

Last Thursday, my customer got the chance to have a customer account meeting with Graham Wood at Oracle Netherlands. Normally this would be a DBA affair, but luckily my customer thought it was a good idea to have two developers join the three DBA's. And I was one of the lucky two developers, since I get to solve quite a lot of performance problems, that are spotted by our DBA's.As a developer I

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com4tag:blogger.com,1999:blog-7395977411859619892.post-27723450837955270202008-04-08T00:10:00.002+02:002008-04-08T00:16:07.566+02:00

Nothing new and shiny this time, but a write up about an aspect of one of the cornerstones of Oracle: read consistency. The concepts manual has a very good chapter about this topic called Data Concurrency and Consistency. A project I was auditing had to select data from multiple tables and interface that data to another system. So I adviced the developers to make sure all the queries would query

Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com1