ORA-22905 when calling ODCI Table functions from PL/SQL

Back in 2015 I experimented using ODCITable* functions to dynamically parse delimited text (see blog post here.)

Now blog reader Daniel Cabanillas points out, that it raises an error when used in PL/SQL. Silly me - I had only tried it out in SQL (my favorite language) and had completely missed that it failed in PL/SQL.

Lucky for me, the classic workaround of dynamic SQL works here too 😁

Participant list for Oracle Dev Gym SQL Championship 2018

2018 is over. Throughout the year people have participated in the "Select for SQL" Tournament on Oracle Dev Gym. It's time for the Top 50 to try their skills in the Annual SQL Championship!

Everybody who tried the quizzes have hopefully had fun as well as exercised SQL skills. Maybe even learned a little :-) I hope you'll all keep working your mental muscles in 2019 as well - get your friends and colleagues to try it too! I'll do my very best to try and pass on knowledge via the quizzes.

Whether you worked out with the quizzes for fun alone or you played in the tournament as well, thank you for being there in 2018. And congratulations to the Top 50 in the "Select for SQL" Tournament on Oracle Dev Gym in 2018 for the stamina and skill displayed to be ranked sufficiently high at the end of the year to participate in the Championship.

ODC Appreciation Day : The ever growing toolbox

It's ODC Appreciation Day 😃 !

The day initiated by Tim Hall where Oracle bloggers worldwide make a blog post to show appreciation of the Oracle Developer Community.

This year I won't dive into a specific technical thing in SQL or PL/SQL, but rather I'm appreciating how much my toolbox grows all the time with new bits coming in a steady flow.

Corrupting characters - How to get invalid byte values stored in strings

Having worked with Database Migration Assistant for Unicode (DMU) to convert some databases from single-byte charactersets to AL32UTF8, I had problems with DMU reporting a lot of characters with invalid byte values (in this case binary values that did not exist in WE8ISO8859P15.)

So how can that happen? Doesn't the database enforce character encoding to match the database characterset?

Wrong - not always.

Well ok, you can get invalid values with single-byte charactersets - but once the database is AL32UTF8 then it can store all the characters in the world, so then it cannot happen, right?

Wrong again - you can still get corrupt character data if you do it the wrong way.

Announcing the results of the Oracle Dev Gym SQL Championship for 2017

A huge round of applause to the top players in the championship:

1st Place: mentzel.iudith of Israel

2nd Place: Pavel Zeman of Czech Republic

3rd Place: Andrey Zaytsev of Russia

Especially noteworthy is the accomplishment of Iudith Mentzel, who also won the DB Design Championship and has been a very regular Dev Gym participant for years. Congratulations, Iudith!

SQL Magic Squares - or Why the Optimizer does not like Magic

A long-time player at the Oracle Dev Gym tried his hand at generating Magic Squares using SQL.

When he attempted to tune his statement a bit, he was surprised that it didn't go a little faster as expected, rather it went from 2 minutes to 45 minutes? At that point he contacted me to see if I could explain the mystery...

Announcing participants in Oracle Dev Gym SQL Championship 2017

Another year, another Oracle Dev Gym SQL Annual Championship. Woohoo!

Players have exercised their mental muscles with the SQL puzzles throughout 2017. Congratulations to everybody digging in all year, trying to figure out the pretzelbenders of me and other quiz authors. And hopefully you've learned something as well, improving your SQL skills.

And a very special congratulations to the Top 50 in the "Select for SQL" Tournament in Oracle Dev Gym in 2017. It takes an effort solving the quizzes succesfully every week to be ranked high at the end of the year.