Video : Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results

Today’s video is a demonstration of returning REF CURSORs from PL/SQL using functions, procedures and implicit statement results.

I was motivated to do this after a conversation with my boss. He’s from a .NET and SQL Server background, and was a bit miffed about not being able to use a SELECT to pass out variable values from a procedure, like you can in T-SQL. So I piped up and said you can using Implicit Statement Results and another myth was busted. I guess most PL/SQL developers don’t use this, and I don’t either, but you should know it exists so you can be a smart arse when situations like this come up. 🙂

The video is based on these articles.

The star of today’s video is Bryn Llewellyn of PL/SQL fame, and more recently Yugabyte.



Implicit Statement Results and Extended Data Types in Oracle 12c…

Over the last couple of days I’ve put another couple of articles live. In this case they are both focused on easing migration from non-Oracle databases to Oracle.

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