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

4 comments:

Niall said...

don't forget the magic of THE in earlier versions (I think 8i, but 8.1.7.4 might not have required it).

From AskTom

</code>
select *
2 from THE
( select cast( in_list('abc, xyz, 012') as
mytableType ) from dual ) a
3 /
</code>

Rob Baillie said...

Nice one Niall, I wish I'd known about that when I was still working on 8i...

William Robertson said...

> For some reason, the TABLE command cannot work out the data type of variable passed to it

A lot of the time it can. I think one of the few remaining cases where it still can't as of 9.2 involves "SELECT *". I would always start without an explicit CAST, and only add it if it was absolutely necessary.

The "THE" syntax was in 8.0, and only valid if you paid extra for the "Object option", which I don't think anyone did.

Rob Baillie said...

Ah haaaaa!

Cheers for the tip, I'll check that out later today...