Posts

Showing posts from January, 2019

Three Hot Tips for Working With Collections

Image
Collections in PL/SQL make it easy for you to implement lists, arrays, stacks, queues, etc. They come in three flavors: associative arrays, nested tables, and varrays. The three types of collections share many features, and also have their own special characteristics.

Here are some tips for making the most of collections. At the bottom of the post, I offer links to a number of resources for diving in more deeply on collections.

You Can Query From Collections

Collections are, for the most part, variables you will declare and manipulate in PL/SQL. But you can query from them using the TABLE operator (and in 12.2 and higher you even leave off that operator).

Use this feature to:
Manipulate table data and in-session collection data within a single SELECT.Use the set-oriented power of SQL on your in-session data.Build table functions (functions that return collections and can be called in the FROM clause of a query. Here's a simple demonstration:
CREATE OR REPLACE TYPE list_of_names_t …

Logic Reigns in the Oracle Dev Gym Logic Championship

Logic is at the very heart of programming, so we complement our quizzes on SQL, PL/SQL and so on with a weekly Logic tournament. And then at the end of the year, the top 50 ranked players qualify for our annual championship.

The following players will be invited to participate in the Logic Annual Championship for 2018, currently scheduled to take place on 19 February.

The number in parentheses after their names are the number of championships in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

NameRankStelios Vlasopoulos (5)1Pavel Zeman (4)2mentzel.iudith (5)3Ludovic Szewczyk (1)4James Su (5)5Chad Lee (5)6Tony Winn (3)7Rytis Budreika (5)8ted (5)9Kanellos (4)10Cor van Berkel (4)11K├Âteles Zsolt (4)12Vijay Mahawar (5)13RalfK (4)14pjas (1)15Mike Tessier (3)16seanm95 (5)17NickL (4)18Michal P. (1)19Eric Levin (4)20Sandra99 (5)21Mehrab (5)22JasonC (5)23Talebian (4)24NielsHecker (5)25richdellheim (5)…

Time for another Dev Gym PL/SQL Championship!

A new year has arrived, and that means that it's time (or will soon be time) for the PL/SQL Challenge Championship, when up to fifty top players from last year's PL/SQL tournament quizzes compete for top honors.

The following players will be invited to participate in the PL/SQL Challenge Championship for 2018, currently scheduled to take place on 21 March (hey, it takes some time to put together five advanced quizzes without any mistakes in them!).

The number in parentheses after their names are the number of championships in which they have already participated (note: from 2010 through 2013, we held quarterly championships for our then daily PL/SQL quiz!).

Congratulations to all listed below on their accomplishment and best of luck in the upcoming championship!


NameRankStelios Vlasopoulos (15)1mentzel.iudith (18)2Tony Winn (7)3NielsHecker (19)4Andrey Zaytsev (7)5patch72 (5)6Ivan Blanarik (12)7siimkask (18)8Rakesh Dadhich (10)9Rytis Budreika (6)10Vyacheslav Stepanov (17)11li_ba…

Working With JSON Arrays in PL/SQL

Image
Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks.

In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods.

Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods.

Here are the methods you are most likely to use:

A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL:

Error Handling Behavior

By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not raised back to your block.

If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greater than 0.

Array I…