Today’s video is a quick run through the Extended Data Types functionality introduced in Oracle Database 12c Release 1. Why such an old subject? Well, it’s a prerequisite for something I was planning to do a video on, so I thought I better do this first. 🙂
For those that don’t like videos, you can find the same information and more here.
It’s a little hard to get excited about either feature as I would not expect any straight Oracle shop to want to use them.
The implicit statement results will be a big bonus for SQL Server migration projects, and those trying to maintain similar APIs for Transact-SQL and PL/SQL, but I don’t see myself using this feature in preference to an explicit out parameter for my own code, for the reasons explained by Tom Kyte here.
My first impression of the 32K VARCHAR2 is I pretty much hate it. I’m very much of the opinion, if you need a CLOB, use a CLOB. I don’t like the lack of control of the LOB segment and I dislike loads of objects in my schema with system generated names. Of course, migration projects will want as little refactoring as possible, so it could be very useful there, which is after all the reason it was added.
I’m a little confused why the limit is 32K. It matches the PL/SQL VARCHAR2 now. It’s bigger than the maximum size of a VARCHAR2(n) in SQL Server, but it is a lot smaller than the VARCHAR(MAX) from SQL Server. I’m thinking I may have missed the point? Maybe I’ll warm to them the next time I do a migration…
Of course, your opinion of any new feature depends very much on what you do on a day to day basis. I reserve the right to do a complete U-turn on this stuff if circumstances change. 🙂