Wednesday, December 11, 2013

See you all in Seattle

In June 2014 I hope to see a whole lot of you at ODTUG's awesome conference Kscope14...

You will not regret going to Kscope14. There is a ton of great content and loads of awesome brainpower to pick ;-) And if you can find the time for it in between all the great stuff, you might consider attending one of my two presentations:
  • Analytic Functions - Advanced Cases
Analytic functions can often help you using data across rows in ways that allow you to solve tasks that otherwise might have called for slowly using the data one row at a time procedurally. The presentation explains a set of advanced usecases of analytic functions showing use of RANK, DENSE_RANK, ROW_NUMBER, RATIO_TO_REPORT, LAG, REGR_SLOPE, ROWS BETWEEN, and more to efficiently solve actual real life tasks, such as top selling items, picking by FIFO, picking route, forecast sales, and more.
  • External Data via HTTP, FTP, Webservices
There are many ways you can access external data from within the Oracle database. The presentation will show ways of using HttpUriType, UTL_HTTP, JPUB, UTL_FTP, UTL_DBWS, APEX_WEB_SERVICE to retrieve external data. Calling SOAP or REST webservices or proprietary formats over http or https (SSL) or FTP will be discussed and shown. There will be demos of some of the method combinations - how many will depend on time available ;-)

Monday, December 2, 2013

SQL Book Club - Any recommendations?

I got a note from Steven Feuerstein the other day about a group of developers in Stockholm starting an SQL Book Club. What a great idea :-) Anyway, they had asked Steven if he had any recommendations for good books on Advanced SQL. And Steven asked me the same question...

And now I'm asking the blog readers: Do you have any favorite books on writing Advanced SQL you can recommend to this new SQL Book Club?

Friday, September 27, 2013

Top selling items - revisited in 12c

April last year I blogged about TOP-N reporting using Top selling items as example. In Oracle 12c we now have a new FETCH FIRST syntax, so in this post I'll revisit the Top selling items example showing where and how FETCH FIRST can be used and where you still need to know the analytic function methods.

Tuesday, September 17, 2013

Active Data Guard and Invalidations

To provide data source for our datawarehouse (in a seperate MS SQL database, god help it, but that's beside the point :-), we have a setup where we have several views where the datawarehouse connection user has been granted select rights.

When we got Active Data Guard in the spring, we let the datawarehouse user connect to the standby instance to offload the quite heavy selects from the production database. But we did get a surprise concerning object invalidation in this setup...

Lars Bo Vanting, the consultant from Blue Gecko that handles most of our DBA work, has written this with a good example on the Blue Gecko blog, so I won't repeat his work. I'll just outline the problem below and refer you to his blog post for more details :-)

Monday, September 16, 2013

Half-day masterclass on Analytic Functions (I hope :-)

I've presented on Analytic Functions twice now - at ODTUG KScope12 and UKOUG 2012. Both times I've felt that an hour is not nearly enough to both teach how to use analytics as well as show use cases of how analytics can really be used for solving a lot of your daily work. Perhaps now I can get a chance to give a half-day masterclass on that topic - at least I am hoping so...

Tuesday, June 4, 2013

What I'm doing end of June 2013

It is that time again...

Time to go mingle with the best of the best, learn much, teach what I can, suffer information overload, have fun, enjoy life, and much much more...

In short - time for ODTUG KScope13 \o/ \o/

Friday, March 1, 2013

PL/SQL Challenge Authorship

The PL/SQL Challenge site by Steven Feuerstein is great for learning various SQL and PL/SQL techniques. I am one of the quiz authors - I write most of the SQL quizzes (and one or two PL/SQL quizzes now and then.)

That means there is now accumulated quite a bit of my work as quizzes - each quiz demonstrating some knowledge of SQL. I could replicate this work as blog posts as well, but instead it is now possible for you to search all my quizzes on PL/SQL Challenge.

I have added that link to the right-hand menu of the blog under "Further SQL Tips" just above "About me". That way anyone interested can easily find more of my SQL work.

I hope it may be useful to you, too. And maybe you find yourself starting to visit the PL/SQL Challenge regurlarly. Even if you aren't very competitive yourself, learning by playing has always proved to be a great way to acquire new knowledge.

Wednesday, February 20, 2013

ROWS versus default RANGE in analytic window clause

I have talked at KScope about the difference between ROWS and RANGE in the analytic window clause, but haven't yet blogged about it. Recently while working out a quiz for the PL/SQL Challenge I discovered yet another reason for remembering to primarily use ROWS and only use RANGE when the actual problem requires it.

Thursday, February 7, 2013

The KScope Charitable Dinner Raffle

Do you want a chance for a dinner with me at KScope13 in New Orleans chatting about SQL? And at the same time get a warm charitable feeling inside helping the volunteers rebuilding New Orleans?

If yes, then read on :-) ...

Thursday, January 3, 2013

Recursive subquery graph traversing

In December a user Silpa asked a question on AskTom on "Bi-directional hierarchical query," which inspired me to fool around with recursive subquery factoring (available from version 11.2) giving Silpa a solution which he seemed to find useful. Since then I've fooled around a little more with it, particularly concerning cycles in the graph data.