Thursday, November 24, 2011

Dynamic lookup via PL/SQL function

If the static Domain-Value-Map (DMV) lookup functionality in SOA Suite is not feasible, because your lookup source is a table in a database, a PL/SQL function might do the trick:

CREATE OR REPLACE FUNCTION LOOKUP
(
LKEY IN VARCHAR2
, LTABLE IN VARCHAR2
, LCOLUMN IN VARCHAR2
, VCOLUMN IN VARCHAR2
) RETURN VARCHAR2 AS
VVALUE VARCHAR2(4000);
BEGIN
EXECUTE IMMEDIATE ('SELECT '||VCOLUMN||' FROM '||LTABLE||' WHERE '||LCOLUMN||'='||LKEY) INTO VVALUE;
RETURN VVALUE;
END LOOKUP;

Now, you can use this function e.g. in a Database Adapter to do a dynamic lookup.
Here an example to lookup the value of column "NAME" in table "PRODUCTS" for key "1" in column "ID":

select lookup('1','PRODUCTS','ID','NAME') from dual;

Any other ideas for dynamic lookups? Would you use a callout in OSB/SOA instead?

No comments:

Post a Comment