Wednesday, October 05, 2005

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: , , , , , , , , , ,

Friday, September 30, 2005

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?

Wednesday, September 28, 2005

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: , , , , , , , ,

Friday, September 23, 2005

Friday afternoon...

Friday afternoons always seem to lead to pointless conversations, which sometimes lead to classic moments of comedy...

During an E-mail conversation about being vegetarian:

"In fact humans aren't carnivores at all. We meat eaters are technically eating carrion, which is meat that is dead for several days or weeks. That's why they hang cows and stuff up to enhance their flavour. We eat carrion, I.E. like vultures and hyeenas. This means we shouldn't kill at all, just eat what we find. I just happen to find it in the supermarket. It's not my fault there's a load of people who go around murdering animals and leave them in shops for me!"

Monday, September 19, 2005

Google maps? PAH!

Ever since Google released the API for Google Maps it seems the whole of the internet has gone crazy for it. Cool things have appeared like an index of the current BBC news stories on a map of the UK. Add to that the fact that Google Earth is pretty tasty too and you'd be forgiven for thinking that no other mapping tools existed.

But... there is, and one of them does something cool and useful that Google Maps doesn't.

I run. Not much, but enough to manage a 10km run without passing out. And when I train I like to make sure that I know exactly how far I'm travelling. I like to plan my training schedule so I know that on Monday I'll do 5km, on Wednesday I'll do 2km fast + 1km jog + 2km fast + 1km jog. I know, I know, I can't help it.

Anyway. Google Maps doesn't help me there. But Map 24...

Map 24 has a really handy little ruler tool. Point and click a rubber band round the route you run and BOSH, the distance travelled rounded to the nearest 100th of a mile (or thereabouts). Very nice for working out 1km / 3km / 5km laps round your local streets.
Oh, and it does a really cool wooshy zoom thing when you put an address in!

Technorati Tags: , , , ,