DATE row generator with DBMS_SCHEDULER

A recent question on the Oracle-L mailing list was about generating dates in a given period. If you had a string like 'MWF', all Mondays, Wednesdays and Fridays between the from- and to-dates should be generated.

That reminded me of a quiz on plsqlchallenge.com I did on using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING. I had created a table function to test calendar expressions - that might be useful for something similar to what was asked on the (more...)

Christmas – A time to give

It's Christmas time... It's a time for giving... It's a time for warm-hearted feelings...

But there are people battling for their lives against cancer, who may not have much energy left to enjoy the holidays. Almost everybody has family or friends whose life has been impacted by cancer. Let us all reach out however we can, help wherever we can, and let good thoughts go out to the victims.

All cancer is terrible - but (more...)

DOUG problem-knuser brainstorm eftermiddagsworkshop og middag

Som et alternativ til kaffemøderne i den danske Oracle brugergruppe DOUG forsøger vi efter idé fra min kollega Majken Sander og undertegnede et nyt koncept uden præsentationer og foredragsholder, men i stedet lade deltagerne brainstorme og sparre med hinanden omkring at knuse et bestemt problem.

Hos thansen.dk køres en aldrende XAL applikation med data i Oracle. Her skal re-nummereres pseudokey for 600 millioner rows pga. applikationen ikke kan håndtere integers over 32 bit. Det (more...)

CSV, XML and JSON parsing – a comparison over versions

Via Steven Feuerstein I was asked to try and give advice concerning fastest way to parse CSV data in PL/SQL. The case was that they had a different database that could be setup to deliver data as a webservice serving either CSV, XML or JSON, and they wished to let their APEX application use PL/SQL to retrieve data from that other database via such webservices. They were not yet on 12.1.0.2.0 (more...)

JSON_TABLE or XMLTABLE – comparison with geocoding

Previously I've demonstrated how to use function XMLTABLE to query Google maps routing directions. Now Oracle version 12.1.0.2.0 has function JSON_TABLE to do similar querying on JSON data rather than XML data. So let's try that out and spot some differences...

For this test we'll use the Google maps geocoding rather than routing directions and try to geocode the address of Oracle headquarters. If we call the Google api with instructions (more...)

Unforgettable In-Memory

Inspired by Maria Colgans quest for In-Memory bumper sticker slogans for OOW14, I couldn't resist a little fun. Musical readers should stop reading now :-)




Sung to the tune of the old Nat King Cole classic


Unforgettable
Like wizardry
Unforgettable
In-Memory

Like the speed of light my query go
Column aggregates just happen so
Never before
Has data been more...

||:
Unforgettable
So cleverly
Is forever more
In-Memory

Row based access, it's a dinosaur

SQL Mini-challenge entry: Kim Berg Hansen v6

by Kim Berg Hansen

Okay, okay, Iggy, you want one buffer get? I’ll do it ;-)

Actually two different versions that might be useful in different circumstances. First one goes like this:

create materialized view log on employees with rowid, commit scn
   (employee_id, job_id, first_name, last_name, department_id)
   including new values;
create materialized view log on departments with rowid, commit scn
   (department_id, location_id)
   including new values;
create materialized view log on locations with rowid, commit scn
    (more...)

SQL Mini-challenge entry: Kim Berg Hansen v5

Iggy Fernandez has been asking why not combine all my three materialized views into one to get a single buffer get.

Sure it is possible to create a single one that is fast refreshable on commit that can be used for query rewrite, as demonstrated by Vlado Barun. The trouble is we have predicates both on name and job title, so we need full index scan on the single mview (which can be done with (more...)

KScope14 – on my way home

For some reasons I got lounge access here at Seattle airport when going home fra KScope14. So I can use the last couple hours here lounging comfortably writing a few words about an awesome week here.

This was my fifth consecutive KScope and I plan to go every year. That is one awesome konference for developers and it just gets better every year. I'll be at KScope15 in Florida for sure! I had a great (more...)

MEMBER OF comparison of PL/SQL and SQL

In the Kscope14 sunday symposium today, Steven Feuerstein explained that MEMBER OF syntax was slow in SQL and fast in PL/SQL. I challenged him that perhaps it was missing indexes on the nested table? My mistake - I got the task of testing it and see if that was the case... So I tested and was surprised at the answer.

I'm creating a nested table type and a table with a column of that type (more...)

Nice evening in Nieuwegein

Thursday I went for a quick trip to Nieuwegein for the Amis KScope Preview. Nice evening :-)

After a train to Copenhagen and a plane to Amsterdam, I checked in at the CitizenM hotel at Shiphol airport. A bit different hotel - toilet and shower in big glass tubes in the room - but actually fairly nice for a single traveller.

Patrick Barel was kind enough to pick me up, so getting to the Amis (more...)

Mini-KScope in Nieuwegein


Can't wait until KScope14 in Seattle?

Or can't make it to Seattle?


Then you can see some of the presentations at a preview at the AMIS offices in Nieuwegein, where some of the KScope14 presenters will try out their presentations in Database development, APEX and ADF tracks. I've been invited to do my presentation on analytic functions, so I'll be there ;-)

It'll be Thursday June 12th 2014. See the agenda here and more details (more...)

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 (more...)

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...

(more...)

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 (more...)

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, (more...)

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 (more...)

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/

Hotel and flight has been booked half a year (more...)

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 (more...)

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.



From my KScope presentation examples here is a simple case of a rolling sum of salaries:

select deptno
, ename
, sal
, sum(sal) over (
partition by deptno
order by sal
) sum_sal
from scott.emp
order by deptno
, sal
/

    DEPTNO ENAME             SAL     (more...)