Lee Lindley

I'm Just Another Perl Hacker who wound up in a big Oracle Database playground

Lee Lindley Scratchpad


11/27/2022Creating Multiple Row Headers with ExcelGen PL/SQL Spreadsheet Generator
ExcelGen prior to version 3 was limited to single row column headers and a single query per worksheet. Now we can create multi-row column headers and title banners. Walking through an example.
11/24/2022Create a PL/SQL Cursor from a Nested Table of Nested Tables
A two dimensional data structure in PL/SQL is most commonly a nested table of record types. For a generic capability of unknown record content or length we need a nested table (rows) of nested tables (columns) structure. Binding such a structure to SQL as a table to extract columns and rows is tricky.
11/13/2022Versioning Oracle Code from Open Source
When Open Source code is already deployed and in use by many production jobs, but you want to implement the latest release, consider adding version identifiers to the object names.
10/09/2022Tuning Query with OR Conditions in a NOT EXISTS
When tuning a query you sometimes run into uncommon problems that do not respond to the bag of tricks you brought to the game like hints and small rewrites. We know that 'OR' conditions can't be hash joined and have seen the optimizer join twice with a 'CONCATENATION'. It doesn't do that for 'anti-joins', so you may have to do something counter intuitive.
09/18/2022Manipulating XLSX Spreadsheets in PL/SQL
Business users often embed their work processes in spreadsheets. A common use case involves data created and maintained by business users in a spreadsheet needing supplemental data from the database. We discuss a method of inputting their spreadsheet to Oracle and outputting it again with supplemental columns from queries while preserving their polymorphic column content.
04/30/2022Using Perl DBD::Oracle to write LOB content
Writing large BLOB/CLOB content on a remote client machine can be done many ways. Let's do it with Perl.
04/29/2022Syntax Highlighting for PL/SQL in vim
Becoming the 'maintainer' of the plsql syntax file for vim wasn't what I set out to do, but wimping out was not in the cards. Now I get the colors I like in vim.
04/28/2022Installing Perl DBD::Oracle on RHL
Installing Perl DBD::Oracle has over the years been a heavy lift. It's still not a button push, but it has gotten better.