Things I have learned this week

A couple of friends and I were joking over the weekend that if we all learned one thing each, every day for a year, then at the end of the year we'd be able to release the book 'Over 1001 things that 3 people learned in a year'

Here's what I learned this week:

• 08/10/2005: Cannon Street London Underground station used to have an ornate glass roof that was removed at the start of WW2. It was moved to the countryside to avoid bomb damage. The barn it was kept in was bombed a week later, completely destroying the glass roof. Cannon Street station remained virtually untouched throughout the whole of the war.


• 09/10/2005: Archduke Franz Ferdinand was assassinated in Sarajevo.


• 10/10/2005: The ship the Queen Elizabeth 2 is always written down with the number 2 rather than the roman numerals II. This is done in order to differentiate it from the Queen with the same name.


• 11/10/2005: The most abundant metal in the human body is calcium


• 12/10/2005: The most abundant bird in the world is the domesticated chicken. In 2003 the US ate 8.2 billion chickens, and if a chicken is grown for 7 weeks before being killed for meat, then in order for the 65 million people in the UK to be able to eat their average of 0.75 chickens a week then a total of 341.5 million chicken must exist at any given time in order to support the market.


• 13/10/2005: It's not enough to learn from your mistakes, you have to actually remember that you learnt from them and put those lessons into action.


• 14/10/2005: Whenever you find a gap in a test and you think "No, I won't put that test in, it'll never happen", it will happen, and it'll probably happen on the live system and give you a big headache.

I cannot make any guarantee as to the accuracy or relevancy of any of the facts, nor will I compensate anyone for the loss of time spent reading them.

Casting a table: variations on a theme

A heads up from William Robertson and I've taken yet another look at the TABLE command (which allows you to issue SQL against a PL/SQL table as if it was a database table). As he points out here, it seems that the TABLE cast has trouble working out the data type when you're doing a SELECT *, but can often work out the data type for the same statement with the column list specified.

Obviously, if I hung around on Ask Tom or Metalink I'd find it much easier to find these nuances out... and I'd do it in a far less public way. But hey, I'd probably forget it then, wouldn't I! ;-)

Anyway, I've put together 3 scripts that some illustrate slight variations that are available. Note I've included DROP statements to get rid of any nasty residue, not to imply that you should drop these types in any real application.

So, first up is a variation that uses a SELECT * and a TABLE + CAST selecting from a PL/SQL table of a primitive datatype

SET SERVEROUTPUT ON SIZE 100000

CREATE TYPE char_table_type AS TABLE OF VARCHAR2(1);
/

DECLARE
  --
  char_table           char_table_type;
  --
  CURSOR cur_tab IS
    SELECT *
    FROM   TABLE( CAST( char_table AS char_table_type ) );
  --
BEGIN
  --
  char_table  :=   char_table_type('A','B','C','D','E','F','G','H','I');
  --
  FOR tab_rec IN cur_tab LOOP
    DBMS_OUTPUT.PUT_LINE( tab_rec.column_value );
  END LOOP;
  --
END;
/

DROP TYPE char_table_type;
/

The second variation uses a SELECT column list, and a TABLE selecting from a PL/SQL table of a primitive datatype. Note that the column name is inferred.

SET SERVEROUTPUT ON SIZE 100000

CREATE OR REPLACE TYPE char_table_type AS TABLE OF VARCHAR2(1);
/

DECLARE
  --
  char_table           char_table_type;
  --
  CURSOR cur_tab IS
    SELECT column_value
    FROM   TABLE( char_table );
  --
BEGIN
  --
  char_table  :=   char_table_type('A','B','C','D','E','F','G','H','I');
  --
  FOR tab_rec IN cur_tab LOOP
    DBMS_OUTPUT.PUT_LINE( tab_rec.column_value );
  END LOOP;
  --
END;
/

DROP TYPE char_table_type;
/

The last variation uses a SELECT column list, and a TABLE selecting from a PL/SQL table of objects. Note that the column name is no longer implied, but there is a requirement to construct objects in order to use this variation.

SET SERVEROUTPUT ON SIZE 100000

CREATE TYPE char_table_row AS OBJECT ( letter VARCHAR2(1) );
/

CREATE TYPE char_table_type AS TABLE OF char_table_row;
/

DECLARE
  --
  char_table           char_table_type;
  --
  CURSOR cur_tab IS
    SELECT letter
    FROM   TABLE( char_table );
  --
BEGIN
  --
  char_table  :=   char_table_type( char_table_row ('A')
     ,char_table_row ('B')
     ,char_table_row ('C')
     ,char_table_row ('D')
     ,char_table_row ('E')
     ,char_table_row ('F')
     ,char_table_row ('G')
     ,char_table_row ('H')
     ,char_table_row ('I') );
  --
  FOR tab_rec IN cur_tab LOOP
    DBMS_OUTPUT.PUT_LINE( tab_rec.letter );
  END LOOP;
  --
END;
/

DROP TYPE char_table_type;
/

DROP TYPE char_table_row;
/

More investigation will take place, and I'm sure this won't be the last post on the topic!

Technorati Tags: , Robert+Baillie, oracle, table, cast, software+development, software, database, 9i, ask+tom, metalink