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 18.104.22.168.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 22.214.171.124.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...
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 and populate it:create type nested_varchar as table of varchar2(100)/
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.
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?
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.I use the same tables of items and monthly sales as in last years blog post:create table items( item varchar2(10) primary key, grp varchar2(10),