Trackback a go go

Thanks to a mail from my good mate Andrew Beacock (why research things when you've got a mate like him to do it for you!), I've think I've got this trackback thing sorted.

For all those people looking for a service out there, I've started using Haloscan. It's pretty easy to setup, Andy's got some help here if you need it, but to be honest it's straight forward.

The difficulty I always had was what to actually do with the trackback once you've got it!

Here's the deal:

When you want to link a blog entry with a trackback facility you start off by writing your blog entry.
Then get hold of the permalink for YOUR entry.
Go to THEIR entry, and click on the trackback link.
With Haloscan you'll then get a trackback URL.
Copy this URL so you now have two links... YOUR entry link and THEIR trackback link
Log into Haloscan and 'Send a Trackback ping'
Paste in YOUR permalink into the (der) 'Your Permalink URL' field, and put THEIR trackback url into the URLs to Ping.

And that should be it.

Check out Andy's post here and it should have a trackback to here...

Technorati Tags: Robert+Baillie, Andrew+Beacock, trackback, blog, Haloscan

Sharing

Andrew Beacock has once again found a little beauty... del.icio.us have added the concept of forwarding bookmarks to other users. I can't wait to see my inbox fill up with useful links from mates, and masses of spam bookmarks from god knows who!

Experimentation is needed me thinks...

Technorati Tags: del.icio.us, Andrew+Beacock, Robert+Baillie, bookmarks

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: Robert+Baillie, oracle, software+development

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: Robert+Baillie, oracle, software+development

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: Robert+Baillie, oracle, software+development