Tuesday, July 12, 2005

The IN Thing, a simpler example

Things are a little simpler when you're moving around PL/SQL tables of data rather than ref cursors. At that point the data used in the table case no longer needs to be available outside of the procedure that uses it. The object and table definitions still do however.
Please forgive the scrolling window...




First we create the table from which we select:

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;


Then we need to define the object and table types that will be used for the communication.


CREATE TYPE gt_id_type
AS OBJECT ( id NUMBER )
/

CREATE TYPE gt_id_list_type AS TABLE OF gt_id_type
/

CREATE TYPE gt_product_type
AS OBJECT ( id NUMBER
, descr VARCHAR2(100 ) )
/

CREATE TYPE gt_product_list_type AS TABLE OF gt_product_type
/


Then the procedures that wil do the selection.

  1. GET_PRODUCTS, which will receive a list of IDs in a GT_ID_LIST_TYPE table, returning the list of products in a GT_PRODUCT_LIST_TYPE.

  2. GET_PRODS_BY_NAME, which will recieve a name and using GET_PRODUCTS will return a list of products whose descr contains the text specified.



You may note that the function GET_ID_LIST, used in the previous example, does not appear. This is since the data held in the ID list table isn't needed outside of GET_PRODUCTS if we prepare teh result set and pass it back in a PL/SQL table.


CREATE OR REPLACE PACKAGE product_pkg AS

FUNCTION get_products ( pt_id_list gt_id_list_type )
RETURN gt_product_list_type;
FUNCTION get_prods_by_name ( pc_product_name VARCHAR2 )
RETURN gt_product_list_type;

END;
/

CREATE OR REPLACE PACKAGE BODY product_pkg AS
--
FUNCTION get_products ( pt_id_list gt_id_list_type )
RETURN gt_product_list_type IS
--
vt_product_tab gt_product_list_type;
--
BEGIN
--
SELECT gt_product_type( id, descr )
BULK COLLECT
INTO vt_product_tab
FROM product
WHERE id IN ( SELECT id FROM TABLE ( pt_id_list ) );
--
RETURN vt_product_tab;
--
END;
--
FUNCTION get_prods_by_name ( pc_product_name VARCHAR2 )
RETURN gt_product_list_type IS
--
vt_product_ids gt_id_list_type;
vt_product_tab gt_product_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, a script to produce some output...


SET SERVEROUTPUT ON SIZE 1000000

DECLARE
--
vt_product_tab gt_product_list_type;
--
BEGIN
--
vt_product_tab := product_pkg.get_prods_by_name( 't' );
--
FOR i IN 1..vt_product_tab.LAST LOOP
DBMS_OUTPUT.PUT_LINE( vt_product_tab( i ).descr );
END LOOP;
--
END;
/


The result should be the same as the previous example...


two
three


Technorati Tags: , ,

Monday, July 11, 2005

The IN Thing: The example

OK, so how do we actually implement the Table cast lookup with a ref cursor?

First, we bear in mind that this is the most complex TABLE cast we can perform, since we need the data and definition to be available outside of the function that is performing the table cast.

The example given has a very simple main cursor, which is effectively:

SELECT *
FROM product


This makes the method appear a little overtly complex. In reality, the main cursor would have to be a lot more complex in order to merit this approach. Additionally, it is most useful when the results are pulled into a higher tier that is not Oracle bound. E.G. An object oriented tier in Java / PHP or the like, where you want to ensure that the record you get back is always in the same form, so you can construct a complete object.

The Example:

Please forgive the scrolling window...



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

  • GET_ID_LIST, which is a helper function used by GET_PRODUCTS in order to make the list of product IDs available to the outside world (so the ref cursor doesn't fail when it's fetched from).



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;
FUNCTION get_id_list RETURN gt_id_list_type;
END;
/

CREATE OR REPLACE PACKAGE BODY product_pkg AS
--
gt_id_list gt_id_list_type;
--
FUNCTION get_id_list RETURN gt_id_list_type IS
BEGIN
RETURN gt_id_list;
END;
--
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( product_pkg.get_id_list()));
--
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


Technorati Tags: , ,

The IN thing

With Oracle 8i and below there was a classic problem that I never really got what I felt was a suitable solution to...

I've always gone for limiting the SQL that exists in both the database and the higher layers, as described (related to PHP)here.

By doing this I find I can very nicely wrap up the database tables with a layer of abstraction that protects the higher layers from changes. So I could have a 'Product' lookup that is based on a reasonably complex query what involves the joining of several tables.
But what if I wanted a list of all the products in the system, or the top 5 priced, or any of those other options that just come up, you've got limited choices.

  1. Produce a function that covers each of the lookups, and include the full SQL statement in each lookup.
  2. Provide a function that covers the return of the list of products ID to return, and pass that list into a function that returns the full details (which can be used for any lookup).
  3. Provide a single function that does the lookup in any case, and has a lot of parameters.
  4. Throw away this whole idea of encapsulation and just put the SQL where you need it.

I've always liked the second option. You can have functions that cover searching for records and then other functions that deal with the returning the details of those records.

One configuration that allows you to do this is to have the function returning the details deal with a single entity. Pass in the ID of the one you're looking for and get the full set of details back. If you need multiple records, then you call the function multiple times. It's a good solution that's clean and simple. It just doesn't perform well when you scale it up. What happens if I want 1,000 records? I call the function 1,000 times?

The alternative would appear to be to pass in a list of the IDs you want and get back a ref cursor or table of records back.
You can reasonably get back a table of results, but it means looping over the incoming IDs and reverting back to your individual lookup.
It's harder to return a ref cursor though: The problem is the inflexibility of the IN statement. It's not possible for you to specify an arbitrary list of values in a non dynamic SQL statement.
I.E. with vc_list_of_ids set to "1, 2, 3, 4", you can't say:

OPEN product_cur FOR
SELECT *
FROM product
WHERE id IN ( vc_list_of_ids );

although you can say:

OPEN product_cur FOR
'SELECT *
FROM product
WHERE id IN ( '|| vc_list_of_ids ||' )';

It's a subtle difference, but the second (the correct one) will require Oracle to reparse the statement every time it is ran. Ask any DBA and they'll say that's not exactly a great idea.

Thankfully, Oracle 9 gave us a much nicer solution. Casting to tables.
We can now use a PL/SQL table of values and treat it as a ful blown SQL table. So, if we use the table vt_list_of_ids, we can now say:

OPEN product_cur FOR
SELECT *
FROM product
WHERE id IN ( SELECT id FROM TABLE( vt_list_of_ids ) );

This has the advantage of not being re-parsed each time.

If we pass this back as an open ref cursor we need to make sure that the table definition and the variable that contains the data are both available to the outside world, but it works a treat.

I'll put a working example up here once I get a chance...

Technorati Tags: , ,

Thursday, July 07, 2005

The BBC don't like parody

I must be missing something, because something pretty minor happened that points to something fairly fundamental. And I just don't understand it.

The BBC shut down IsAGaylord.com .

Now forgive me if I'm going mad here, but a week ago this site was getting 20,000 hits a day from people who got a surprise, had an immature snigger, then noticed that it wasn't really a BBC news site after all. Maybe they forwarded it on, maybe they didn't, but they went away not even considering that it was in any way linked to the BBC and definitely not thinking any the worse of the BBC if they did. Then a couple of people didn't get it, and complained... to the BBC.

And so the lawyers got involved, put some pressure onto the host and the site got pulled. So now, for the next week that site will get its usual 20,000 hits a day, and 140,000 people will read a tirade from a guy who's had to pull a very successful site because the BBC got petty.

So, in order to keep the BBC's reputation with a handful of people, they've managed to tell 140,000 how petty they are.

It takes a lawyer to be THAT clever!

Tuesday, July 05, 2005

Blog stats made easy

Thanks to Andy Beacock's post here I've put some more logging onto the site. At the risk of looking like a right newbie, I've even left the counter on for the world to see... watch that number creep up to 10 over a matter of mere months!

Statcounter will very nicely put together some pretty comprehensive stats on page visits, unique visitors, re-visitors and the like. Split down by day, shown in a nice graph. It's basically got all the little facts about your visitors that let you know if people are actually reading what you've got to say.

As with Andy, I'm hoping that seeing the numbers will push me to get more useful text out there...

The same has, of course, been put onto BobaPhotoBlog

Technorati Tags: , , , ,