PL/SQL Syntax Highlighting in Textpad – for when you can’t play with Penguins

When I’m mucking around at home, it’s Linux all the way. Unfortunately, at work ( in desktop terms, at least) I’m still stuck with being a Microsofty.

This leaves me with Textpad as my program editor of choice.

As promised then, here’s the quick and dirty guide to enabling syntax highlighting for PL/SQL in Textpad ( works for versions 4 and 5) … Continue reading

Customising gedit for PL/SQL on Ubuntu 9.10

As I’ve said before, when it comes to doing serious Database programming, I prefer to work in SQL*Plus.

Yes, SQLDeveloper ( and Toad for that matter) are great for looking at stored program units, running ad-hoc queries etc, but I find there’s no substitute for being “close to the metal” when it comes to in-depth or complex programming in PL/SQL.

Apart from anything else, the feedback you get at the prompt is the actual error, and not masked by something the IDE is objecting to.
Line numbering also helps a lot. If the compiler reports an error at line 100, I want to be able to go to line 100 in my code directly, no messing. Text editors tend to do this stuff really well.

At work, being stuck on Windows, I have to rely on the trusty Textpad, which is perfectly adequate.
Fortunately, when I get home, it’s Ubuntu all the way. This means getting to play with another of those unobtrusive Linux gems – gedit. Continue reading

Sociable PL/SQL – associative arrays indexed by VARCHAR2

PL/SQL tables are a really good way of array processing in PL/SQL. In 99% of cases, using a table indexed by PLS_INTEGER works a treat, but what about those times when life would be that bit easier if you could index by VARCHAR2 ?

I was recently presented with one such instance, courtesy of some unconventional table design by now long-departed wise-men.
These gurus had decided to use a synthetic key on a reference table…except the natural key was still in the table….but the actual primary key was half of the natural key plus the synthetic key.

Oh how I would’ve loved to meet these ancients and received the benefit of their wisdom, with the aid of a spot-light, some gaffer tape…and a very sharp stick.

This is a theme I will return to in more detail in a future post ( yep, the 10 things that really wind me up post, coming after the quick ‘n’ dirty tuning post I promised a couple of months ago).

Anyway, back to the PL/SQL table madness.

In this example, we have a table called COUNTRIES – not very original, but hey, the pub’s open so we haven’t got time to hang around.

CREATE TABLE countries(
code VARCHAR2(3),
country VARCHAR2(50))
/

The COUNTRY table has the following records :-


SQL> select * from countries;
CODE COUNTRY
---- -----------------------
AUS AUSTRALIA
BEL BELGIUM
FRA FRANCE
GBR UNITED KINGDOM
GER GERMANY
JPN JAPAN
ITA ITALY
USA UNITED STATES
NZL NEW ZEALAND


9 rows selected.

Now, we have a situation where we want to use the country code for three of those countries, we know which three we want to get. But we then want to use them programatically elsewhere without having to faff about working out which order they are in in our array….

--
-- associate.sql
-- Script to demonstrate a PL/SQL associative array 
-- indexed by something other  than a number.
--
set serveroutput on
DECLARE
   TYPE typ_country_codes IS TABLE OF countries.code%TYPE 
      INDEX BY countries.country%TYPE;
   tbl_country_codes typ_country_codes;
   --
   -- In this instance, we want all the English speaking 
   -- countries... except for the USA
   -- To paraphrase the colonel in Apocalypse Now - 
   -- "Yankee don't play cricket !"
   --
   CURSOR c_countries IS
      SELECT code, country
      FROM countries
      WHERE country in ('AUSTRALIA', 'NEW ZEALAND', 
         'UNITED KINGDOM');
BEGIN
   FOR r_countries IN c_countries LOOP
      -- 
      -- assign the code to the table using the value of 
      -- the country column as the index
      --
      tbl_country_codes(r_countries.country) := 
         r_countries.code;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE( 'Country code for Australia is '
      ||tbl_country_codes('AUSTRALIA'));
   DBMS_OUTPUT.PUT_LINE( 'Country code for New Zealand is '
      ||tbl_country_codes('NEW ZEALAND'));
   DBMS_OUTPUT.PUT_LINE( 'Country code for United Kingdom is '
      ||tbl_country_codes('UNITED KINGDOM'));
END;
 /

And that’s stumps.
Please let me know if you find a less tortuous circumstance in which you find this useful.