PL/SQL Varray

For the first time since beginning my career in Software Development nearly 4 years ago, I have used a PL/SQL varray on the job.

You laugh! At my previous job, almost every data structure was abstracted from us, and we all know why. PL/SQL data structures are ugly, obtuse, and non-intuitive. I’ve been writing some database cleanup scripts and needed a way to store the values from a particular list that aren’t referenced by another table, and the varray fit the bill. Too bad I couldn’t write it in Python, which would have saved half the effort.

You know what’s even more insane? I built a PL/SQL table of records and it took me nearly half an hour to figure out that when you “index by” a variable of type ‘table’, you can’t use the extend() method on it, you simply assign values to any given table subscript.

It’s the small things in life.

Update 4/20/2017:

Fail. I actually was not able to use the Varray because you cannot use local collections in SQL statements in a PL/SQL block. Oracle architecture confuses me. I could have looped through the varray and inserted each item individually into a SQL statement, but I ended up using the PL/SQL table datatype instead. Maybe one day I’ll get to use the varray. I have, however, been able to learn a lot about associative arrays. It feels good to know about these things.

Advertisements