Questionable Search...

I know I've been quiet for a while, but I'm still here. There's things I'm going to get round to saying sooner or later, but a recent move has meant a distinct lack of internet access at home.

For now though... my referrer tracking uncovered a hit to the site from an unexpected search, one that I'm not sure how to deal with...

What does smug mean?

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

If I had a hammer...

I'm sure that pretty much every experienced developer out there has heard the proverb:

• If the only tool you have is a hammer, then everything's a nail.

Well, I've heard a few variations on the theme and so I've decided to start collecting them...

• Sometimes it's just a nail, and all you need is a hammer.


• Don't ignore your hammer just because you heard someone else hit their thumb with theirs.


• When you're in the market for a new tool, make sure you're not buying yet another hammer.


• You can enhance the usefulness of your hammer by adding a chisel.


• A Rotary Electro-pneumatic hammer delivering 4600 hammer blows a minute is still just a hammer.

Anyone got any more?

The In Thing - Simplified

I've had a few people visit the site looking for some help on the Oracle 9i TABLE command. With this, you can issue SELECT statements against PL/SQL tables.

Now, previously I'd posted about the self same command, in a series of posts here:

http://robertbaillie.blogspot.com/2005/07/in-thing.html
http://robertbaillie.blogspot.com/2005/07/in-thing-example.html
http://robertbaillie.blogspot.com/2005/07/in-thing-simpler-example.html

Soon, I plan to put a few more notes together, starting from a simpler example. But for now I'll just thank Amis for the post here, which has pointed out a complexity in my own example that I didn't realise...

For some reason, the TABLE command cannot work out the data type of variable passed to it, and so will tend to throw an exception:
ORA-22905: cannot access rows from a non-nested table item

As I'd only ever done this when working with package, I previously worked around this by coding a function that returns the content of the relevent package variable. TABLE has no problem working out the datatype returned from a function.

It turns out I could have got away with just a simple CAST.
E.G. SELECT * FROM TABLE( CAST( table_variable AS table_type ) )

So, with that in mind, the first 'In Thing' example becomes this:

The Example:

In order to have something to access, we need the tables and data:

CREATE TABLE product
  ( id     NUMBER
  , descr  VARCHAR2( 100 ) )
/

INSERT INTO product ( id, descr ) VALUES ( 1, 'one' );
INSERT INTO product ( id, descr ) VALUES ( 2, 'two' );
INSERT INTO product ( id, descr ) VALUES ( 3, 'three' );
INSERT INTO product ( id, descr ) VALUES ( 4, 'four' );
INSERT INTO product ( id, descr ) VALUES ( 5, 'five' );
INSERT INTO product ( id, descr ) VALUES ( 6, 'six' );

COMMIT;

In order to perform the cast we need to declare a type for the row in the table, and then the table type itself:

CREATE TYPE gt_id_type AS OBJECT ( id NUMBER )
/

CREATE TYPE gt_id_list_type AS TABLE OF gt_id_type
/

Then we define the package that will perform the cast.
We have the following functions

• GET_PRODUCTS, which is passed a table of IDs, and returns a ref cursor containing the products requested.


• GET_PRODS_BY_NAME, which is passed a string, and returns all the products that have a description containing that string. This function uses GET_PRODUCTS to return the product details

CREATE OR REPLACE PACKAGE product_pkg AS
  TYPE gt_product_cur IS REF CURSOR;
  FUNCTION get_products ( pt_id_list  gt_id_list_type )
                                    RETURN gt_product_cur;
  FUNCTION get_prods_by_name ( pc_product_name   VARCHAR2 )
                                    RETURN gt_product_cur;
END;
/

CREATE OR REPLACE PACKAGE BODY product_pkg AS
  --
  gt_id_list     gt_id_list_type;
  --
  FUNCTION get_products
    ( pt_id_list  gt_id_list_type )
    RETURN gt_product_cur IS
    --
    vt_product_cur gt_product_cur;
    --
  BEGIN
    --
    gt_id_list := pt_id_list;
    --
    OPEN vt_product_cur FOR
      SELECT *
      FROM   product
      WHERE  id IN ( SELECT id
                     FROM   TABLE( CAST( gt_id_list AS gt_id_list_type ) ) );
    --
    RETURN vt_product_cur;
    --
  END;
  --
  FUNCTION get_prods_by_name
    ( pc_product_name   VARCHAR2 )
    RETURN gt_product_cur IS
    --
    vt_product_ids   gt_id_list_type;
    --
  BEGIN
    --
    SELECT  gt_id_type( id )
    BULK COLLECT
    INTO    vt_product_ids
    FROM    product
    WHERE   descr LIKE '%'|| pc_product_name|| '%';
    --
    RETURN get_products( vt_product_ids );
    --
  END;
  --
END;
/

Finally, we have some code to run the GET_PRODS_BY_NAME function and return its set of values.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  --
  vt_cur          product_pkg.gt_product_cur;
  vr_product_rec  product%ROWTYPE;
  --
BEGIN
  --
  vt_cur := product_pkg.get_prods_by_name( 't' );
  --
  LOOP
    --
    FETCH vt_cur INTO vr_product_rec;
    EXIT WHEN vt_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( vr_product_rec.descr );
    --
  END LOOP;
  --
END;
/

And the output:

two
three

Disclaimer: I've not yet managed to test this out with access from a PHP layer...

Technorati Tags: Robert+Baillie, oracle, table, cast, software+development, software, database, 9i, Amis