A short while ago I created a SQL quiz for Oracle Dev Gym (PL/SQL Challenge) demonstrating the use of temporal validity and VERSIONS PERIOD FOR syntax to create a "change report" for changes in item prices. Use of temporal validity makes this easier, shorter and more readable code, but even though I thought I had it all covered, player Iudith Mentzel pointed out a tiny quirk I'd overlooked.
I've been making SQL quizzes for years now over at the Oracle Dev Gym (or PL/SQL Challenge as it started out as.) Every year I really have to work my few braincells hard when it is time for the annual championships. The players that have made it through at year and managed to rank in the top are awesome at SQL, so I have to write some really hard quizzes for the championship.
A little while ago a member of the Danish Oracle User Group DOUG asked if anyone had a bit of experience with use of R together with Oracle Database. I remembered an article I wrote for OTech Magazine on forecasting with R and Oracle and thought it would be a good primer for him. OTech Magazine is no longer active, sadly, but I thought I could send him a link anyway to my article. That was not to be :-(
The native JSON functionality in Oracle Database version 12.2 has evolved quite a bit since the JSON functions first appeared in Oracle Database version 18.104.22.168. Just one example is JSON_TABLE can be used as a bridge between the open standard GeoJSON format and the database internal SDO_GEOMETRY format, making it very simple to use externally available geocoding datasources within spatial applications in the database.
You all know Oracle OpenWorld, right? It's a behemoth of a conference filling up the streets and hotels of San Francisco with many thousands of people moving like ants between conference rooms, checking out exhibitors, hanging around lounging areas, networking, talking, listening, eating, drinking, learning and having fun. And it happens every year...
I'm back after one of the best ocean voyages ever - the OUGN Spring Conference 2016. That conference is arranged by Oracle User Group Norway and is two days from Thursday before lunch-time to Saturday before lunch-time. The special thing is, that it all happens on board a cruise ship that uses 20 hours to sail from Oslo, Norway to Kiel, Germany - stays in Kiel 4 hours - then 20 hours to sail back.
Right smack in the middle of the busiest time of the year for the business (this time of year we sell fireworks for New Year) I have opportunity to reflect a bit on the upcoming year and what 2016 will bring of challenges and joys.
Of course things will happen in the Oracle world that'll indirectly influence my year, as always. Not always possible to predict here near the start of the year, but most likely at least there'll be a release of version 12.2 of the database, with the usual fun of working with the new features of SQL and PL/SQL.
I got a question recently from Morten Braten whether my FIFO analytic techniques could be helpful for calculating the value of fuel consumption for cargo vessels. It turned out not to be quite the same, but similar. Analytic functions definitely could help. I'll show how in two parts. First this blog post will calculate the fuel consumption in quantity, then part two will calculate the value by FIFO.
In 2 days time I fly off to San Francisco for Oracle OpenWorld 2015. Last time I went to OpenWorld was 2008, so I'm looking forward to a re-visit and see how much bigger it has become :-)Like everybody else I've been making a schedule for the week. Of course things will happen to change the schedule along the way, but some things I definitely have on the must-do list...
In Part 1 I demonstrated how to "scrape" data from the live webpages with the votes of the Oracle Database Developer Choice Awards. Unfortunately those webpages are not ideal for scraping, so I promised a workaround. Here I do a semi-manual scraping af data by using a browser to retrieve the actual HTML, save it to a file, and then let the code parse out the data from the file.
You know about the Oracle Database Developer Choice Awards voting, right? If not, then go vote now, please, and then come back and read on :-) I want to be able to query the voting status, who is ranked what in each category and so on. But I don't want to type in votes in a table, especially not when they change all the time. Instead I'd like to query the live data in the HTML webpages. Can that be done? Yes it can...
Oracle is celebrating the Oracle Community by having us developers nominate and vote forOracle Database Developer Choice Awards to our fellow developers in five database development technologies:SQL, PL/SQL, ORDS, APEX, Db-DesignThe nomination period is over and it has been possible to vote since September 15th.
I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12. Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches. But can there be uses where we want overlapping or even nested matches? Well, I found at least one case where I think it makes perfect sense...
Supposing you've got data as a text string with "row" and "column" delimiters and you would like to parse out those columns and rows. The external table syntax would actually be great for this, but that requires writing the text out to a file first, and then you can import it back again as columns and rows. There are various other alternatives using SQL and/or XML manipulations, but one method that I don't see much used is to use the Data Cartridge functionality to in a sense expand the SQL language to do this.
In my last post, I wrote about a dream of a European version of ODTUG Kscope conference - working title "Escope." ;-) We're trying to determine whether it would be feasible to create such a conference - if you haven't already, go and fill out the survey and help us find out if there's basis for the dream.
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.
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 when the victims are children, it breaks your heart.
UPDATE:Da hveranden tilmeldte indtil nu bedst kan tirsdag 2. december og de andre også kan den dag, så er det hermed vedtaget at det bliver tirsdag vi mødes, nørder om nummerserier, og får en gang mad og drikke.Nedenstående er derfor rettet til.
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 22.214.171.124.0 so they did not have JSON_TABLE, and they seemed to find that do-it-yourself parsing of CSV data was the fastest.
Previously I've demonstrated how to use function XMLTABLE to query Google maps routing directions. Now Oracle version 126.96.36.199.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.
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!
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...
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 offices was easy.
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.
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:
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.
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.
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.
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 ODTUGKScope13 \o/ \o/Hotel and flight has been booked half a year ago, passport and ESTA is current and valid, I'm ready to go!So the end of June I will be doing the following:
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.
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 :-) ...In January ODTUG started a little competition where you could win a dinner for two at KScope13 in New Orleans by telling about your favorite experience from KScope.
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.
So, I'm about to leave UKOUG 2012. I had a good time and learned quite a bit from the smart people gathered in Birmingham ;-)Thank you to those attending my presentation on analytic functions - I hope you learned something from it. If you need to take a closer look, both presentation and scripts can be found here.Birmingham Airport next stop...
This is part 2 of a three part posting on analytic FIFO picking of multiple orders. Part 2 shows an alternative way of doing the same thing as part 1 did - but this time using recursive subquery factoring in Oracle v.
Answering a question on the OTN forum was a bit tricky to get an analytic sum using a RANGE BETWEEN that would handle leap years, but in the end I came up with a workaround that satisfies the requirement.